/**
 * All Rights Reserved , Copyright (C) 2020 , 青岛鼎信通讯股份有限公司
 * <p>
 * MpmProjectService
 * 项目立项
 * <p>
 * 修改纪录
 * 2020-12-25 版本：1.0 lanjianwei 创建。
 *
 * @version 版本：1.0
 * @author 作者：lanjianwei
 * 创建日期：2020-12-25
 */
package com.topscomm.mpm.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.topscomm.basic.exception.ServiceException;
import com.topscomm.basic.util.SQLAdapter;
import com.topscomm.cbo.pojo.*;
import com.topscomm.cbo.service.ICboApprovalNodeInsService;
import com.topscomm.cbo.service.ICboDeptService;
import com.topscomm.cbo.service.ICboItemDetailsService;
import com.topscomm.cbo.service.ICboItemsService;
import com.topscomm.cbo.service.impl.CboAttachmentService;
import com.topscomm.main.hessian.IBaseOperateRecordService;
import com.topscomm.main.hessian.IMmsDeptExtraInfoService;
import com.topscomm.main.hessian.IMmsProvinceCityMatchinfoService;
import com.topscomm.mis.pojo.MisDeptArchEntity;
import com.topscomm.mis.service.IMisDeptArchService;
import com.topscomm.mpm.MpmSystemConst;
import com.topscomm.mpm.dto.MpmProjectSaleDTO;
import com.topscomm.mpm.pojo.*;
import com.topscomm.mpm.service.IMpmProjectCustomerService;
import com.topscomm.mpm.service.IMpmProjectProductInfoService;
import com.topscomm.mpm.utils.ConvertTimeUtil;
import com.topscomm.mpm.vo.MpmProjectTaskDetailsVO;
import com.topscomm.pub.util.*;
import com.topscomm.pub.vo.Page;
import com.topscomm.pub.vo.Query;
import com.topscomm.pub.vo.ResponseResult;
import com.topscomm.tap.common.DatacodeCache;
import com.topscomm.tap.common.FlowDatacodeCache;
import com.topscomm.tap.common.SessionUserBean;
import com.topscomm.tap.common.TapSystemConst;
import com.topscomm.tap.security.SecurityUserDetails;
import com.topscomm.tap.threadlocal.ThreadLocalContext;
import org.apache.commons.beanutils.ConvertingWrapDynaBean;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Scope;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestParam;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Service(value = "mpmProjectPolymerizationService")
@Scope(value = "singleton")

public class MpmProjectPolymerizationService {
    @Resource(name = "mpmProjectService")
    private MpmProjectService mpmProjectService;
    @Resource(name = "mpmProjectSaleService")
    private MpmProjectSaleService mpmProjectSaleService;
    @Resource(name = "datacodeCache")
    private DatacodeCache datacodeCache;
    @Autowired
    private IMisDeptArchService misDeptArchService;
    @Resource(name = "flowDatacodeCache")
    protected FlowDatacodeCache flowDatacodeCache;
    @Autowired
    private CboAttachmentService cboAttachmentService;
    @Autowired
    private IMmsDeptExtraInfoService  mmsDeptExtraInfoService;
    @Autowired
    private MpmProjectMilepostService mpmProjectMilepostService;
    @Autowired
    private IMpmProjectProductInfoService mpmProjectProductInfoService;
    @Autowired
    @Qualifier("securityUserDetailsService")
    private UserDetailsService userDetailsService;
    @Autowired
    private ICboDeptService cboDeptService;
    @Autowired
    private ICboApprovalNodeInsService cboApprovalNodeInsService;
    @Autowired
    private MpmProjectAppointmentService mpmProjectAppointmentService;
    @Autowired
    private MpmProjectPlanService mpmProjectPlanService;
    @Autowired
    private MpmProjectChangeService mpmProjectChangeService;
    @Autowired
    private MpmProjectProductOutInfoService mpmProjectProductOutInfoService;
    @Autowired
    private MpmProjectSuperviseService mpmProjectSuperviseService;
    @Autowired
    private IMpmProjectCustomerService mpmProjectCustomerService;
    @Autowired
    private IMmsProvinceCityMatchinfoService mmsProvinceCityMatchinfoService;
    @Autowired
    private MpmProjectBidDateService mpmProjectBidDateService;
    @Autowired
    private MpmProjectBidScoreService mpmProjectBidScoreService;
    @Autowired
    private MpmProjectCustomerDecisionService mpmProjectCustomerDecisionService;
    @Autowired
    private MpmProjectTaskService mpmProjectTaskService;
    @Autowired
    private MpmProjectOverdueRecordService mpmProjectOverdueRecordService;
    @Autowired
    IBaseOperateRecordService baseOperateRecordService;
    @Autowired
    private ICboItemsService iCboItemsService;
    @Autowired
    private ICboItemDetailsService iCboItemDetailsService;
    @Autowired
    private MpmProjectReviewService mpmProjectReviewService;
    @Autowired
    private MpmProjectRiskService mpmProjectRiskService;
    @Autowired
    private MpmProjectVisitExpertService mpmProjectVisitExpertService;
    @Autowired
    private MpmProjectNoMeetWeekService mpmProjectNoMeetWeekService;

    /**
     * @Title: insert
     * @description:
     * @param paraMap
     * @return
     * @author: lanjianwei
     * @date: 2020年12月29日上午11:27:40
     * @modify:
     */
    @Transactional(rollbackFor = Exception.class)
    public Map<String, Object> insert(@RequestBody(required = false) Map<String, Object> paraMap) throws Exception {
        Map<String, Object> dataMap = paraMap;
        Map<String, Object> dataMapTemp = new HashMap<String, Object>();
        Map<String, Object> dataMaps = new HashMap<String, Object>();
        // 插入project主表
        MpmProjectEntity mpmProjectEntity = new MpmProjectEntity();
        // 如果存在原项目等级，且原项目等级和现项目等级不一致，说明项目等级发生了更改，此时需要更新levelhistory字段
        String oldprojectlevel = ConvertUtil.convertToString(paraMap.get("oldprojectlevel"));
        String newprojectlevel = ConvertUtil.convertToString(paraMap.get("projectlevel"));
        if (!StringUtil.isEmpty(oldprojectlevel) && !newprojectlevel.equals(oldprojectlevel)) {
            int oldlevel = Integer.parseInt(oldprojectlevel);
            int newlevel = Integer.parseInt(newprojectlevel);
            JSONObject record = new JSONObject();
            String[] levelArray = {"A", "B", "C", "D"};
            record.put("oldprojectlevel", levelArray[oldlevel]);
            record.put("newprojectlevel", levelArray[newlevel]);
            dataMap.put("levelhistory", record.toString());
        }
        // 获取省区编码并赋值
        if (!dataMap.containsKey("deptcommoncode")) {
            List<Map<String, Object>> deptCommonCodeList = this.mpmProjectService.queryMap(dataMap, "getDeptCommonCode");
            if (!deptCommonCodeList.isEmpty()) {
                dataMap.put("deptcommoncode", deptCommonCodeList.get(0).get("deptcommoncode"));
            } else {
                throw new ServiceException("运营部门未录入，请联系管理员！");
            }
        }
        mpmProjectEntity.convertFromMap(dataMap);
        mpmProjectEntity = mpmProjectService.insert(mpmProjectEntity);
        long id = mpmProjectEntity.getId();
        dataMaps = mpmProjectEntity.convertToMap();
        // 插入附加表
        MpmProjectSaleEntity mpmProjectSaleEntity = new MpmProjectSaleEntity();
        mpmProjectSaleEntity.convertFromMap(dataMap);
        mpmProjectSaleEntity.setId(id);
        mpmProjectSaleEntity = mpmProjectSaleService.insert(mpmProjectSaleEntity);
        dataMapTemp = mpmProjectSaleEntity.convertToMap();
        if (!dataMapTemp.isEmpty()) {
            dataMaps.putAll(dataMapTemp);
        }
        // 为paraMap放入id
        paraMap.put("id", id);
        // 新增或更新产品信息记录
        this.mpmProjectProductInfoService.removeAndAddRecord(paraMap);
        // 新增客户信息
        if (paraMap.containsKey("customerlist") && paraMap.get("customerlist") != null) {
            JSONArray jsonArray = (JSONArray) paraMap.get("customerlist");
            List<Map> customerList = JSONObject.parseArray(jsonArray.toJSONString(), Map.class);
            customerList.forEach(cus -> {
                cus.put("sourceid", id);
                cus.put("id", 0);
            });
            this.mpmProjectCustomerService.removeAndAddRecord(customerList);
        }
        // 新增或更新外部产品信息记录
        this.mpmProjectProductOutInfoService.removeAndAddRecordForProductOut(paraMap);
        // 新增或更新挂网开标日期信息记录
//		this.mpmProjectBidDateService.removeAndAddBidDate(paraMap.get("projectBidDate"),id);
        // 新增或更新客户决策信息
        this.mpmProjectCustomerDecisionService.removeAndAddCustomerDecision(paraMap.get("projectCustomerDecision"), id);
        return dataMaps;
    }

    /**
     * @Title: update
     * @description:
     * @param paraMap
     * @return
     * @author: lanjianwei
     * @date: 2020年12月29日上午11:50:02
     * @modify:
     */
    @Transactional(rollbackFor = Exception.class)
    public Map<String, Object> update(@RequestBody(required = false) Map<String, Object> paraMap) throws Exception {
        Map<String, Object> dataMap = paraMap;
        // 获取省区编码并赋值
        if (!dataMap.containsKey("deptcommoncode")) {
            List<Map<String, Object>> deptCommonCodeList = this.mpmProjectService.queryMap(dataMap, "getDeptCommonCode");
            if (!deptCommonCodeList.isEmpty()) {
                dataMap.put("deptcommoncode", deptCommonCodeList.get(0).get("deptcommoncode"));
            } else {
                throw new ServiceException("运营部门未录入，请联系管理员！");
            }
        }
        MpmProjectEntity updateEntity = new MpmProjectEntity();
        // 需要先转化成实体再更新，防止前台传递的值为Json格式从而更新失败
        Object o = paraMap.get(MpmProjectEntity.FieldProductrange);
        if (o instanceof JSONArray) {
            paraMap.put(MpmProjectEntity.FieldProductrange, o.toString());
        }
        Object obj = paraMap.get(MpmProjectEntity.FieldProductoutrange);
        if (obj instanceof JSONArray) {
            paraMap.put(MpmProjectEntity.FieldProductoutrange, obj.toString());
        }
        // 如果存在原项目等级，且原项目等级和现项目等级不一致，说明项目等级发生了更改，此时需要更新levelhistory字段
        String oldprojectlevel = ConvertUtil.convertToString(paraMap.get("oldprojectlevel"));
        String newprojectlevel = ConvertUtil.convertToString(paraMap.get("projectlevel"));
        if (!StringUtil.isEmpty(oldprojectlevel) && !newprojectlevel.equals(oldprojectlevel)) {
            int oldlevel = Integer.parseInt(oldprojectlevel);
            int newlevel = Integer.parseInt(newprojectlevel);
            JSONObject record = new JSONObject();
            String[] levelArray = {"A", "B", "C", "D"};
            record.put("oldprojectlevel", levelArray[oldlevel]);
            record.put("newprojectlevel", levelArray[newlevel]);
            paraMap.put("levelhistory", record.toString());
        }
        mpmProjectService.update(paraMap);
        // 新增或更新产品信息记录
        MpmProjectEntity entity = new MpmProjectEntity();
        entity.convertFromMap(paraMap);
        this.mpmProjectProductInfoService.removeAndAddRecord(paraMap);
        // 新增或更新外部产品信息记录
        this.mpmProjectProductOutInfoService.removeAndAddRecordForProductOut(paraMap);
        // 新增或更新挂网开标日期信息记录
//		this.mpmProjectBidDateService.removeAndAddBidDate(paraMap.get("projectBidDate"),entity.getId());
        // 修改附加表
        mpmProjectSaleService.update(dataMap);
        // 新增客户信息
        if (paraMap.containsKey("customerlist")) {
            JSONArray jsonArray = (JSONArray) paraMap.get("customerlist");
            List<Map> customerList = JSONObject.parseArray(jsonArray.toJSONString(), Map.class);
            customerList.forEach(cus -> {
                cus.put("sourceid", entity.getId());
                cus.put("projectcode", entity.getProjectcode());
            });
            this.mpmProjectCustomerService.removeAndAddRecord(customerList);
        }
        // 新增或更新客户决策信息
        this.mpmProjectCustomerDecisionService.removeAndAddCustomerDecision(paraMap.get("projectCustomerDecision"), entity.getId());
        return paraMap;
    }

    /**
     * @Title: load
     * @description:
     * @param paraMap
     * @return
     * @author: lanjianwei
     * @date: 2020年12月29日下午3:08:01
     * @modify:
     */
    public Map<String, Object> load(@RequestParam(required = false) Map<String, Object> paraMap) {
        Map<String, Object> dataMap = mpmProjectService.queryMapById(paraMap.get("id").toString());
        Map<String, Object> dataMapsale = mpmProjectSaleService.queryMapById(paraMap.get("id").toString());
        if (dataMapsale.isEmpty()) {
            return null;
        }
        dataMapsale.putAll(dataMap);
        dataMap = dataMapsale;
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle("SOURCEID='{0}' and SOURCETYPE='{1}' ", paraMap.get("id"), MpmProjectEntity.tableName);
        List<CboAttachmentEntity> cboAttachmentEntities = cboAttachmentService
                .queryByWhere(sql.toString());
        dataMap.put("cboattachmentcount", cboAttachmentEntities.size());
        dataMap.put("customerlist", this.mpmProjectCustomerService.queryMapByWhere(sql.toString()));
        // 获取内部产品、外部产品子表信息
        sql.clear();
        sql.appendSingle(" projectid = '{0}' ", ConvertUtil.convertToString(paraMap.get("id")));
        dataMap.put("productlist", mpmProjectProductInfoService.queryMapByWhere(sql.toString()));
        dataMap.put("productoutlist", mpmProjectProductOutInfoService.queryMapByWhere(sql.toString()));
        return dataMap;
    }

    /**
     * @Title: delete
     * @description:
     * @param paraMap
     * @return
     * @author: lanjianwei
     * @date: 2020年12月30日下午2:15:47
     * @modify:
     */
    @Transactional(rollbackFor = Exception.class)
    public String delete(Map<String, Object> paraMap) {
        paraMap = BaseUtil.decodeSecureMap(paraMap);
        String ids = ConvertUtil.convertToString(paraMap.get("ids"));
        // 先删除产品信息
        List<MpmProjectEntity> mpmProjectEntities = this.mpmProjectService.queryByIds(ids);
        String projectcodes = mpmProjectEntities.stream().map(item -> item.getProjectcode())
                .collect(Collectors.joining("','", "'", "'"));
        mpmProjectProductInfoService.deleteByWhere(MpmProjectProductInfoEntity.FieldProjectcode +
                " in (" + projectcodes + ") ");
        // 删除主表和附加表单据
        mpmProjectService.deleteByWhere(" id in (" + ids + ")");
        mpmProjectSaleService.deleteByWhere(" id in (" + ids + ")");
        // 删除客户信息
        StringBufferProxy whereSql = new StringBufferProxy();
        whereSql.appendSingle(" sourcetype = '{0}' and sourceid in ({1}) ", MpmProjectEntity.tableName, ids);
        this.mpmProjectCustomerService.deleteByWhere(whereSql.toString());
        return "删除成功";
    }

    /**
     * @Title: list
     * @description:
     * @param paraMap
     * @return
     * @author: lanjianwei
     * @date: 2020年12月29日下午3:50:45
     * @modify:
     */
    public Page list(@RequestParam(required = false) Map<String, Object> paraMap,
                     SessionUserBean userBean) {
        paraMap = BaseUtil.decodeSecureMap(paraMap);
        Query query = new Query(paraMap);
        String whereSql = this.spellListSql(paraMap, userBean);
        Map<String, Object> tempMap = new HashMap<>();
        int currentPage = query.getCurrentPage();
        int pageSize = query.getPageSize();
        int begincount = currentPage * pageSize - pageSize;
        tempMap.put("sidx", query.getSidx());
        tempMap.put("sord", query.getSord());
        tempMap.put("pagesize", pageSize);
        tempMap.put("begincount", begincount);
        tempMap.put("whereSql", whereSql);
        // 查询项目总数
        int totalCount = mpmProjectService.getCount(whereSql + " and EXISTS (SELECT id FROM MpmProjectSale WHERE MPMPROJECT.id = MpmProjectSale.id)");
        List<Map<String, Object>> tempList = null;
        if (query.getPageSize() > 0) {
            tempList = mpmProjectService.queryMapForPage(tempMap, "queryPolymorizationMapForPage");
        } else
            tempList = mpmProjectService.queryMapByWhere(whereSql, "queryPolymarizationMapByWhere");
        // 超期项目返回当前里程碑及任务
        if (paraMap.get("qry_currentmilepost") != null) {
            setCurrentMilePost(tempList);
        }
        List<Map<String, Object>> resultList = new ArrayList<>();
        for (Map<String, Object> mapRow : tempList) {
            setDispFields(mapRow);
            resultList.add(mapRow);
        }
        return new Page(resultList, totalCount, query.getPageSize(), query.getCurrentPage());
    }


    /**
     * @author: wudaliang
     * @date: 2021-03-12
     * @param: paraMap
     * @param: userBean
     * @description: 项目追踪页面
     * @return: com.topscomm.pub.vo.ResponseResult
     * @modify:
     */
    public Page listForTrack(Map<String, Object> paraMap, SessionUserBean userBean) {
//		paraMap = BaseUtil.decodeSecureMap(paraMap);
        Query query = new Query(paraMap);
        String whereSql = this.spellListSqlForTrack(paraMap, userBean);
        Map<String, Object> tempMap = new HashMap<>();
        int currentPage = query.getCurrentPage();
        int pageSize = query.getPageSize();
        int begincount = currentPage * pageSize - pageSize;
        tempMap.put("sidx", query.getSidx());
        tempMap.put("sord", query.getSord());
        tempMap.put("pagesize", pageSize);
        tempMap.put("begincount", begincount);
        tempMap.put("whereSql", whereSql);
        // 查询项目总数
        int totalCount = mpmProjectService.getCount1(tempMap, "queryPolymarizationMapByWhere");
        List<Map<String, Object>> tempList = null;
        if (query.getPageSize() > 0) {
            tempList = mpmProjectService.queryMapForPage(tempMap, "queryPolymorizationMapForPage");
        } else {
            tempList = mpmProjectService.queryMapByWhere(whereSql, "queryPolymarizationMapByWhere");
        }
        if (CollectionUtils.isEmpty(tempList)) {
            return new Page(tempList, totalCount, query.getPageSize(), query.getCurrentPage());
        }
        String projectCodes = getProjectCodes(tempList);
        // 项目管理页查询所有可查看项目各自的督办情况
        setSuperviseListForList(tempList, projectCodes);
        // 项目管理页需要返回立项、任命、策划相关时间
        getVariousDateForSuperVise(tempList);
        // 项目管理页需要返回项目/里程碑及任务/任务超期时间、次数
        this.getOverdueTimeAndNum(tempList);
        // 项目管理页需要返回客户名称
        this.getClientName(tempList);
        // 查询是否关注
        tempList = baseOperateRecordService.getMarkedData(tempList, MpmProjectEntity.tableName, getUserBean().getUser().getCode());
        // 查询A级项目是否存在新客户决策分析
        getCustomerTaskForList(tempList, paraMap);
        List<Map<String, Object>> resultList = new ArrayList<>();
        for (Map<String, Object> mapRow : tempList) {
            setDispFields(mapRow);
            resultList.add(mapRow);
        }
        // 查询进入短名单信息
        this.queryDmdInfoForList(tempList);
        return new Page(resultList, totalCount, query.getPageSize(), query.getCurrentPage());
    }


    /**
     * @Title: list
     * @description:
     * @param paraMap
     * @return
     * @author: lanjianwei
     * @date: 2020年12月29日下午3:50:45
     * @modify:
     */
    public Page listForQuery(Map<String, Object> paraMap, String whereSql) {
        if(!paraMap.containsKey("opensid")) {
            paraMap = BaseUtil.decodeSecureMap(paraMap);
        }
        Query query = new Query(paraMap);
        Map<String, Object> tempMap = new HashMap<>();
        int currentPage = query.getCurrentPage();
        int pageSize = query.getPageSize();
        int begincount = currentPage * pageSize - pageSize;
        tempMap.put("sidx", query.getSidx());
        tempMap.put("sord", query.getSord());
        tempMap.put("pagesize", pageSize);
        tempMap.put("begincount", begincount);
        tempMap.put("whereSql", whereSql);
        // 查询项目总数
        int totalCount = mpmProjectService.getCount1(tempMap, "queryPolymarizationMapByWhere");
        List<Map<String, Object>> projectList = null;
        if (query.getPageSize() > 0 && !paraMap.containsKey("notQueryForPageFlag")) {
            projectList = mpmProjectService.queryMapForPage(tempMap, "queryPolymorizationMapForPage");
        } else
            projectList = mpmProjectService.queryMap(tempMap, "queryPolymarizationMapByWhere");
        if (CollectionUtils.isEmpty(projectList)) {
            return new Page(projectList, totalCount, query.getPageSize(), query.getCurrentPage());
        }
        // 如果projectlist的个数>1000，则将projectlist按照1000分成多个List
        List<List<Map<String, Object>>> splitList = new ArrayList<>();
        int toIndex = 1000;
        for (int i = 0; i < projectList.size(); i+= 1000) {
            if (i + 1000 > projectList.size()) {
                toIndex = projectList.size() - i;
            }
            splitList.add(projectList.subList(i, i + toIndex));
        }
        for (List<Map<String, Object>> tempList : splitList) {
            String projectCodes = getProjectCodes(tempList);
            // 导出数据时获取督办情况、机会点调用此函数
            if (paraMap.containsKey("notQueryForPageFlag")) {
                setSuperviseListForExcel(tempList, projectCodes);
                setChanceClueForExcel(tempList);
            } else {
                // 项目监管查询所有可查看项目各自的督办情况
                setSuperviseListForList(tempList, projectCodes);
            }
            // 项目监管查询复盘相关信息（项目结果、结果公布日期、结项金额）
            this.getProjectReviewResult(tempList);
            // 查询项目风险信息
            this.getProjectRiskInfo(tempList);
            // 获取任务信息
            this.queryProjectTaskInfo(tempList);
            // 获取专家拜访数量
            this.queryProjectVisitExpertInfo(tempList);
            // 获取标记信息
            this.setSignInfo(tempList);
            // 拼接产品信息
            this.queryProductList(tempList);
            for (Map<String, Object> mapRow : tempList) {
                setDispFields(mapRow);
            }
            // 导出数据时，处理产品名称、复盘会召开日期、会议应开次数、标记内容
            if (paraMap.containsKey("notQueryForPageFlag")) {
                this.setProductRangeForExcel(tempList);
                this.setMeetingInfoForExcel(tempList, ConvertUtil.convertToString(paraMap.get("meetToken")));
            }
        }
        List<Map<String, Object>> resultList = new ArrayList<>();
        splitList.forEach(resultList::addAll);
        return new Page(resultList, totalCount, query.getPageSize(), query.getCurrentPage());
    }


    /**
     * @author: wudaliang
     * @date: 2021-03-09
     * @param: paraMap
     * @description: 查询主表和附加表的codeMap
     * @return: com.topscomm.pub.vo.ResponseResult<java.lang.Object>
     * @modify:
     */
    public ResponseResult<Object> datacodeList(Map<String, Object> paraMap) {
        Map<String, Object> codeMap = new HashMap<String, Object>();
        // 主表编码列表
        List<Map<String, Object>> flowidList = flowDatacodeCache.getApprovalFlowListMap("ApprovalFlow_MpmProject", this.getUserBean(), "sortcode");
        codeMap.put("flowidList", flowidList);
        List<Map<String, Object>> stateList = datacodeCache.getCodeListMap("TapApprovalBillState", "", "sortcode");
        codeMap.put("stateList", stateList);
        List<Map<String, Object>> enabledList = datacodeCache.getCodeListMap("CboYesNo", "", "sortcode");
        codeMap.put("enabledList", enabledList);
        List<Map<String, Object>> projectlevelList = datacodeCache.getCodeListMap("MpmProjectLevel", "", "sortcode");
        codeMap.put("projectlevelList", projectlevelList);
        List<Map<String, Object>> projectstateList = datacodeCache.getCodeListMap("MpmProjectState", "", "sortcode");
        codeMap.put("projectstateList", projectstateList);
        List<Map<String, Object>> mpmEndTypeList = datacodeCache.getCodeListMap("MpmEndType", "", "sortcode");
        codeMap.put("mpmEndTypeList", mpmEndTypeList);
        List<Map<String, Object>> mpmApprovalTypeList = datacodeCache.getCodeListMap("MpmApprovalType", "", "sortcode");
        codeMap.put("mpmApprovalTypeList", mpmApprovalTypeList);
        List<Map<String, Object>> mpmProjectAbnormalStateList = datacodeCache.getCodeListMap("MpmProjectAbnormalState", "", "sortcode");
        codeMap.put("mpmProjectAbnormalStateList", mpmProjectAbnormalStateList);
        // 部门名称编码，由于redis未存储sortcode，为了防止deptcommoncode顺序混乱,从数据库中查询deptcommoncode
        List<Map<String, Object>> deptcommoncode1List = new ArrayList<Map<String, Object>>();
        CboItemsEntity deptItemsEntity = iCboItemsService.queryObjectByWhere("code = 'DeptCommonCodeTrack'");
        List<CboItemDetailsEntity> deptCommonCodes = iCboItemDetailsService.queryByWhere("itemid=" + deptItemsEntity.getId() + " and enabled=1 order by sortcode asc");
        for (CboItemDetailsEntity codeItem : deptCommonCodes) {
            Map<String, Object> addItem = new HashMap<>();
            addItem.put("id", codeItem.getId());
            addItem.put("code", codeItem.getCode());
            addItem.put("name", codeItem.getName());
            deptcommoncode1List.add(addItem);
        }
        codeMap.put("deptCommonCodeList", deptcommoncode1List);
        // 项目监管的督办情况筛选条件
        List<Map<String, Object>> superviseStateList = datacodeCache.getCodeListMap("MpmSuperviseState", "", "sortcode");
        Map<String, Object> superviseState = new HashMap<String, Object>();
        superviseState.put("code", 3);
        superviseState.put("name", "无督办");
        superviseState.put("sortcode", 3);
        superviseStateList.add(superviseState);
        codeMap.put("superviseStateList", superviseStateList);

        // 附加表编码列表
        List<Map<String, Object>> MpmPurchasePatternList = datacodeCache.getCodeListMap("MpmPurchasePattern", "", "sortcode");
        codeMap.put("MpmPurchasePatternList", MpmPurchasePatternList);
        List<Map<String, Object>> projectSourceList = datacodeCache.getCodeListMap("ProjectSource", "", "sortcode");
        codeMap.put("projectSourceList", projectSourceList);
        // 是否需要系统部监管编码
        List<Map<String, Object>> needsupportList = datacodeCache.getCodeListMap("CboYesNo", "", "sortcode");
        codeMap.put("needsupportList", needsupportList);
        // 销售项目状态大类编码
        List<Map<String, Object>> projectlargestateList = datacodeCache.getCodeListMap("MpmProjectLargeState", "", "sortcode");
        codeMap.put("projectlargestateList", projectlargestateList);
        // 春季/秋季攻势编码
        List<Map<String, Object>> projectattackList = datacodeCache.getCodeListMap("MpmProjectAttack", "", "sortcode");
        codeMap.put("projectattackList", projectattackList);
        // 是否进入短名单
        List<Map<String, Object>> intonamelist = datacodeCache.getCodeListMap("MpmIntoNameList", "", "sortcode");
        codeMap.put("intonameList", intonamelist);
        // 产品线
        List<Map<String, Object>> productlinelist = datacodeCache.getCodeListMap("ProductLine", "", "sortcode");
        codeMap.put("productlineList", productlinelist);
        // PMO节点项目类型编码
        List<Map<String, Object>> pmoprojecttypeList = datacodeCache.getCodeListMap("MpmPMOProjectType", "", "sortcode");
        codeMap.put("pmoprojecttypeList",pmoprojecttypeList);
        // 项目结果编码
        List<Map<String, Object>> projectresultList = datacodeCache.getCodeListMap("MpmProjectResult", "", "sortcode");
        codeMap.put("projectresultList",projectresultList);
        // 产品大类编码
        List<Map<String, Object>> productcatalog1List = datacodeCache.getCodeListMap("ProductCatalog1", "", "sortcode");
        codeMap.put("productcatalog1List",productcatalog1List);
        // 产品中类编码
        List<Map<String, Object>> productcatalog2List = datacodeCache.getCodeListMap("ProductCatalog2", "", "sortcode");
        codeMap.put("productcatalog2List",productcatalog2List);
        return ResponseResult.ok(codeMap);
    }


    /**
     * @Title: spellListSql
     * @description:转换出查询SQL语句
     * @param paraMap
     * @param userBean
     * @return
     * @author: lanjianwei
     * @date: 2020年12月30日下午2:27:00
     * @modify:
     */
    protected String spellListSql(Map<String, Object> paraMap, SessionUserBean userBean) {
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle(" mpmproject.enabled=1 ");
        if (paraMap.containsKey("qry_projectname")) {
            String value = paraMap.get("qry_projectname").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.projectname like '%{0}%' ", value);
        }
        if (paraMap.containsKey("qry_projectcode")) {
            String value = paraMap.get("qry_projectcode").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and projectcode = '{0}' ", value);
        }
        if (paraMap.containsKey("qry_projectstate")) {
            String value = paraMap.get("qry_projectstate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.projectstate in ({0})", value);
        }
        if (paraMap.containsKey("qry_abnormalstate")) {
            String value = paraMap.get("qry_abnormalstate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and abnormalstate in ({0})", value);
        }
        if (paraMap.containsKey("qry_projectlevel")) {
            String value = paraMap.get("qry_projectlevel").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.projectlevel in ({0})", value);
        }
        if (paraMap.containsKey("qry_projectmanager")) {
            String value = paraMap.get("qry_projectmanager").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and (mpmproject.projectmanager like '%{0}%' or mpmproject.projectmanagercode like '%{0}%') ", value);
        }
        if (paraMap.containsKey("qry_projectmanagercode")) {
            String value = paraMap.get("qry_projectmanagercode").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.projectmanagercode={0}", value);
        }
        if (paraMap.containsKey("qry_state")) {
            String value = paraMap.get("qry_state").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.state={0}", value);
        }
        if (paraMap.containsKey("qry_deptcommoncode")) {
            String value = paraMap.get("qry_deptcommoncode").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.deptcommoncode in ({0}) ", value);
        }
        if (paraMap.containsKey("qry_docno")) {
            String value = paraMap.get("qry_docno").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.docno like '%{0}%'", value);
        }
        if (paraMap.containsKey("qry_begindocdate")) {
            String value = paraMap.get("qry_begindocdate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.docdate >= {0} ", SQLAdapter.toDateLong(value + " 00:00:00"));
        }
        if (paraMap.containsKey("qry_enddocdate")) {
            String value = paraMap.get("qry_enddocdate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.docdate <= {0}", SQLAdapter.toDateLong(value + " 23:59:59"));
        }
        if (paraMap.containsKey("qry_deptname")) {
            String value = paraMap.get("qry_deptname").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.deptname like '%{0}%' ", value);
        }
        if (paraMap.containsKey("qry_open")) {
            String value = paraMap.get("qry_open").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.open = 1", value);
        }
        if (paraMap.containsKey("qry_chancecode")) {
            String value = paraMap.get("qry_chancecode").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle(" and mpmproject.id in (select id from MpmProjectSale where chanceclue = '{0}' " +
                        "and projectsource = '2')", value);
            }
        }
        if (paraMap.containsKey("qry_chancecode_formpm")) {
			String value = paraMap.get("qry_chancecode_formpm").toString();
			if (!StringUtil.isEmpty(value)) {
				StringBufferProxy chancesql = new StringBufferProxy();
				chancesql.appendSingle("select leadofmpmprojectcode from ltcchancebackbone where code = '{0}'", value);
				List<Map<String, Object>> chanceLists =mpmProjectService.selectExecute(chancesql.toString());
				if(chanceLists.size()>0) {
					String[] leadofmpmprojectcodes = ConvertUtil.convertToString(chanceLists.get(0).get("leadofmpmprojectcode")).split(",");
					if(leadofmpmprojectcodes.length==0) {
						sql.appendSingle(" and mpmproject.id in (select id from MpmProjectSale where chanceclue = '{0}'and projectsource = '2')", value);
					} else {
						String mpmprojectcodes = "";
						for(String str : leadofmpmprojectcodes) {
							mpmprojectcodes = mpmprojectcodes + "'"+str+"',";
						}
						sql.appendSingle(" and (mpmproject.id in (select id from MpmProjectSale where chanceclue = '{0}'and projectsource = '2')"
								+ " or mpmproject.projectcode in ({1}))", value,mpmprojectcodes.substring(0,mpmprojectcodes.length()-1));
					}
				} else {
					sql.appendSingle(" and mpmproject.id in (select id from MpmProjectSale where chanceclue = '{0}'and projectsource = '2')", value);
				}
			}
		}
        if (paraMap.containsKey("qry_mpmprojectcodeforbid")) {
            String value = paraMap.get("qry_mpmprojectcodeforbid").toString();
            if (!StringUtil.isEmpty(value)) {
                String[] mpmprojectcodessourcebid = ConvertUtil.convertToString(value).split(",");
                if(mpmprojectcodessourcebid.length==1) {
                    sql.appendSingle(" and mpmproject.projectcode in ('{0}')", value);
                } else {
                    String mpmprojectcodes = "";
                    for(String str : mpmprojectcodessourcebid) {
                        mpmprojectcodes = mpmprojectcodes + "'"+str+"',";
                    }
                    sql.appendSingle(" and mpmproject.projectcode in ({0})", mpmprojectcodes.substring(0,mpmprojectcodes.length()-1));
                }
            }
        }
        if (paraMap.containsKey("qry_where")) {
            String value = paraMap.get("qry_where").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and {0}", value);
        }
        if (userBean.getUser().getAdminlevel() < 1)// 管理员、超级管理员，不控制权限
        {
            // fromProject 1 项目立项页面
            if (paraMap.containsKey("fromProject")) {
                String value = (String) paraMap.get("fromProject");
                if ("1".equals(value)) {
                    sql.appendSingle(" and (usercode = '{0}')", userBean.getUser().getCode());
                }
            }
        }
        // qry_permission org公司，inChargeMe我负责的，participate我参与的
        if (paraMap.containsKey("qry_permission")) {
            String value = (String) paraMap.get("qry_permission");
            sql.appendSingle("and " + mpmProjectService.whereExistsSqlForIndex("MpmProject", "projectcode", value));
        }
        return sql.toString();
    }

    /**
     * @author: wudaliang
     * @date: 2021-03-12
     * @param: paraMap
     * @param: userBean
     * @description: 项目追踪页面
     * @return: java.lang.String
     * @modify:
     */
    protected String spellListSqlForTrack(Map<String, Object> paraMap, SessionUserBean userBean) {
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle(" mpmproject.enabled=1 ");
        if (paraMap.containsKey("qry_projectname")) {
            String value = paraMap.get("qry_projectname").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and (projectname like '%{0}%' or projectcode like '%{0}%') ", value);
        }
        if (paraMap.containsKey("qry_projectcode")) {
            String value = paraMap.get("qry_projectcode").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and projectcode like '%{0}%' ", value);
        }
        if (paraMap.containsKey("qry_abnormalstate")) {
            String value = paraMap.get("qry_abnormalstate").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle(" and abnormalstate in ({0})", value);
            }
        }
        if (paraMap.containsKey("qry_projectlevel")) {
            String value = paraMap.get("qry_projectlevel").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and projectlevel in ({0})", value);
        }
        if (paraMap.containsKey("qry_projectmanager")) {
            String value = paraMap.get("qry_projectmanager").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and (projectmanager like '%{0}%' or projectmanagercode like '%{0}%') ", value);
        }

        if (paraMap.containsKey("qry_state")) {
            String value = paraMap.get("qry_state").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.state={0}", value);
        }
        if (paraMap.containsKey("qry_docno")) {
            String value = paraMap.get("qry_docno").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.docno like '%{0}%'", value);
        }
        if (paraMap.containsKey("qry_begindocdate")) {
            String value = paraMap.get("qry_begindocdate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.docdate >= {0} ", SQLAdapter.toDateLong(value + " 00:00:00"));
        }
        if (paraMap.containsKey("qry_enddocdate")) {
            String value = paraMap.get("qry_enddocdate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.docdate <= {0}", SQLAdapter.toDateLong(value + " 23:59:59"));
        }
        if (paraMap.containsKey("qry_deptname")) {
            String value = paraMap.get("qry_deptname").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.deptname like '%{0}%' ", value);
        }
        if (paraMap.containsKey("query_createdepart")) {
            String value = paraMap.get("query_createdepart").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.deptname  in ({0})", value);
        }
        if (paraMap.containsKey("qry_owner")) {
            String value = paraMap.get("qry_owner").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.owner like '%{0}%' ", value);
        }
        if (paraMap.containsKey("qry_sponsor")) {
            String value = paraMap.get("qry_sponsor").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.sponsor like '%{0}%' ", value);
        }
        if (paraMap.containsKey("qry_bidcycle")) {
            String value = paraMap.get("qry_bidcycle").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.bidcycle  in ('{0}')", value);
        }
        if (paraMap.containsKey("startDateForFinisthDate")) {
            String value = paraMap.get("startDateForFinisthDate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.finishdate> {0} ", SQLAdapter.toDateShort(value));
        }
        if (paraMap.containsKey("endDaterFinisthDate")) {
            String value = paraMap.get("endDaterFinisthDate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.finishdate< {0} ", SQLAdapter.toDateShort(value) + " + 1");
        }
        if (paraMap.containsKey("startDateForActualEndDate")) {
            String value = paraMap.get("startDateForActualEndDate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.actualenddate> {0} ", SQLAdapter.toDateShort(value));
        }
        if (paraMap.containsKey("endDateForActualEndDate")) {
            String value = paraMap.get("endDateForActualEndDate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.actualenddate< {0} ", SQLAdapter.toDateShort(value));
        }
        if (paraMap.containsKey("qry_deptcommoncode")) {
            String value = paraMap.get("qry_deptcommoncode").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.deptcommoncode in ({0}) ", value);
        }
        // 挂网日期筛选
        if (paraMap.containsKey("startDateForBidBeginDate") && paraMap.containsKey("endDateForBidBeginDate")) {
            String startDateForBidBeginDate = paraMap.get("startDateForBidBeginDate").toString(),
                    endDateForBidBeginDate = paraMap.get("endDateForBidBeginDate").toString();
            sql.appendSingle(" and bidbegindate between {0} and {1} ", SQLAdapter.toDateShort(startDateForBidBeginDate), SQLAdapter.toDateShort(endDateForBidBeginDate));
        }
        // 开标日期筛选
        if (paraMap.containsKey("startDateForBidEndDate") && paraMap.containsKey("endDateForBidEndDate")) {
            String startDateForBidEndDate = paraMap.get("startDateForBidEndDate").toString(),
                    endDateForBidEndDate = paraMap.get("endDateForBidEndDate").toString();
            sql.appendSingle(" and bidenddate between {0} and {1} ", SQLAdapter.toDateShort(startDateForBidEndDate), SQLAdapter.toDateShort(endDateForBidEndDate));
        }
        // 结项类型筛选
        if (paraMap.containsKey("qry_endtype")) {
            String value = paraMap.get("qry_endtype").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle("and endtype in ({0})", value);
            }
        }
        if (paraMap.containsKey("qry_pmoprojecttype")) {
            String value = paraMap.get("qry_pmoprojecttype").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle("and pmoprojecttype in ({0})", value);
            }
        }
        // 项目阶段筛选
        if (paraMap.containsKey("qry_projectstate")) {
            String value = paraMap.get("qry_projectstate").toString();
            if (!StringUtil.isEmpty(value)) {
                //因为该查询是两个字段合并查询，所以需要重新解析页面传值
                //解析页面传值，将数字全部去出
                Pattern pattern = Pattern.compile("[^0-9]");
                Matcher matcher = pattern.matcher(value);
                String temp = matcher.replaceAll("");
                StringBufferProxy projectstateValue = new StringBufferProxy();
                StringBufferProxy schedulestateValue = new StringBufferProxy();
                for (int i = 0; i < temp.length(); i++) {
                    String status = temp.substring(i, i + 1);
                    switch (status) {
                        case "0":
                        case "1":
                        case "2":
                        case "3":
                        case "4":
                        case "8":
                            projectstateValue.appendSingle("'{0}',", status);
                            break;
                        case "5":
                        case "6":
                        case "7":
                        case "9":
                            schedulestateValue.appendSingle("'{0}',", status);
                            break;
                    }
                }

                if (StringUtils.isNotBlank(projectstateValue.toString()) && StringUtils.isBlank(schedulestateValue.toString())) {
                    sql.appendSingle(" and projectstate  in ({0})  ",
                            projectstateValue.substring(0, projectstateValue.length() - 1));
                } else if (StringUtils.isBlank(projectstateValue.toString()) && StringUtils.isNotBlank(schedulestateValue.toString())) {
                    sql.appendSingle(" and schedulestate  in ({0})  ",
                            schedulestateValue.substring(0, schedulestateValue.length() - 1));
                } else {
                    sql.appendSingle(" and (projectstate  in ({0}) or schedulestate  in ({1}) ) ",
                            projectstateValue.substring(0, projectstateValue.length() - 1),
                            schedulestateValue.substring(0, schedulestateValue.length() - 1));
                }

            }
        }
        // 工作组成员筛选
        if (paraMap.containsKey("qry_decisionusername")) {
            String value = paraMap.get("qry_decisionusername").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and EXISTS (SELECT ID FROM MPMPROJECTTEAM WHERE MPMPROJECT.PROJECTCODE = MPMPROJECTTEAM.PROJECTCODE AND (MPMPROJECTTEAM.USERCODE like '%{0}%' OR MPMPROJECTTEAM.USERNAME like '%{0}%') AND MPMPROJECTTEAM.STATE = 9  AND MPMPROJECTTEAM.ENABLED = 1) ", value);
        }
        // 线索/机会点筛选
        if (paraMap.containsKey("qry_projectsource")) {
            String value = paraMap.get("qry_projectsource").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and projectsource in ({0}) ", value);
        }
        // 督办情况筛选
        if (paraMap.containsKey("qry_supervisestate")) {
            String value = paraMap.get("qry_supervisestate").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle("and (");
                // 若有选择无督办筛选条件则先执行（3为无督办对应编号）
                if (value.contains("3")) {
                    sql.appendSingle("(NOT EXISTS (SELECT ID FROM MPMPROJECTSUPERVISE supervise WHERE" +
                            " supervise.PROJECTCODE = MPMPROJECT.PROJECTCODE))");
                    // 若除了选择无督办筛选情况还选择了其他选项，则要把字符串进行截取。
                    // 因为无督办情况代表督办表无当前项目的督办记录，其他选项都是有督办记录的。
                    // 如下截取无需遍历字符串，提高代码效率
                    if (value.length() > 1) {
                        sql.appendSingle(" or ");
                        // 因为字符串排列顺序不同，若无督办情况不是第一个选中的选项则进行第一种截取（例如"0,1,3", "2,3,1"等情况）
                        // 若第一个选中的是无督办情况则进行第二种截取。主要区别在于逗号的位置不同。（例如"3,1,0"等情况）
                        if (value.indexOf("3") != 0) {
                            value = value.replace(",3", "");
                        } else {
                            value = value.replace("3,", "");
                        }
                    }
                }
                // 筛选进行中、超期、已完成三个状态的督办情况
                if (!value.contains("3")) {
                    sql.appendSingle("(EXISTS (SELECT ID FROM MPMPROJECTSUPERVISE supervise WHERE" +
                            " supervise.PROJECTCODE = MPMPROJECT.PROJECTCODE AND supervise.SUPERVISESTATE IN ({0})))", value);
                }
                sql.appendSingle(")");
            }
        }
        // 项目状态大类筛选
        if (paraMap.containsKey("qry_projectlargestate")) {
            String value = paraMap.get("qry_projectlargestate").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle(" and ( 1!=1 ");
                // 包括 projectstate 0 的状态 ，即立项中
                if (value.contains("0")) {
                    sql.appendSingle("or ((projectmanagercode = '{0}' OR usercode = '{0}') AND (projectstate IS NULL)) or projectstate = '{1}'", this.getUserBean().getUser().getCode(), MpmSystemConst.ProjectState.StartProjectCode);
                }
                // 包括 projectstate 等于1 即进行中状态
                if (value.contains("1")) {
                    sql.appendSingle(" or projectstate in ({0}) ", MpmSystemConst.ProjectState.ExecuteStateCode);
                }
                // 包括 projectstate 等于2或8 即完成、异常完成状态
                if (value.contains("2")) {
                    sql.appendSingle(" or projectstate in ({0}) ", MpmSystemConst.ProjectState.NormalFinish + "," + MpmSystemConst.ProjectState.AbnormalFinish);
                }
                // 包括 projectstate 等于3，即立项不通过状态
                if (value.contains("3")) {
                    sql.appendSingle(" or projectstate in ({0}) ", MpmSystemConst.ProjectState.UnstartProjectCode);
                }
                sql.appendSingle(" ) ");
            }
        }
        if (paraMap.containsKey("qry_comments")) {
            int value = ConvertUtil.convertToInt(paraMap.get("qry_comments"));
            if (MpmSystemConst.EnableEnum.Enabled == value) {
                sql.appendSingle("and MPMPROJECT.id in (select ID from MPMPROJECT a\n" +
                        "where exists ( select 1 from MPMCOMMENTS b left join COMMENTSPERMISSIONRECORD c on b.id = c.SOURCEID where a.ID = b.SOURCEID and b.SOURCETYPE = 'MpmProject' and instr(',' || c.AUTHORIZEUSERCODES || ',', ',' || '{0}' || ',') > 0\n" +
                        "          ) union all select ID  from MPMPROJECT a\n" +
                        "where exists(select 1 from MPMPROJECTTASK b left join MPMCOMMENTS c on  b.id = c.SOURCEID and c.SOURCETYPE = 'MpmProjectTask' left join COMMENTSPERMISSIONRECORD d on d.SOURCEID = c.ID where a.PROJECTCODE = b.PROJECTCODE  and instr(',' || d.AUTHORIZEUSERCODES || ',', ',' || '{0}' || ',') > 0))", this.getUserBean().getUser().getCode());
            } else {
                sql.appendSingle("and MPMPROJECT.id not in (select ID from MPMPROJECT a\n" +
                        "where exists ( select 1 from MPMCOMMENTS b left join COMMENTSPERMISSIONRECORD c on b.id = c.SOURCEID where a.ID = b.SOURCEID and b.SOURCETYPE = 'MpmProject' and instr(',' || c.AUTHORIZEUSERCODES || ',', ',' || '{0}' || ',') > 0\n" +
                        "          ) union all select ID  from MPMPROJECT a\n" +
                        "where exists(select 1 from MPMPROJECTTASK b left join MPMCOMMENTS c on  b.id = c.SOURCEID and c.SOURCETYPE = 'MpmProjectTask' left join COMMENTSPERMISSIONRECORD d on d.SOURCEID = c.ID where a.PROJECTCODE = b.PROJECTCODE  and instr(',' || d.AUTHORIZEUSERCODES || ',', ',' || '{0}' || ',') > 0))", this.getUserBean().getUser().getCode());
            }
        }
        if (paraMap.containsKey("qry_focus")) {
            int value = ConvertUtil.convertToInt(paraMap.get("qry_focus"));
            if (MpmSystemConst.EnableEnum.Enabled == value) {
                sql.appendSingle("and MPMPROJECT.id in (select ID from MPMPROJECT a\n" +
                        "where exists ( select 1 from BaseOperateRecord b  where a.ID = b.SOURCEID and b.SOURCETYPE = 'MpmProject' and b.usercode = '{0}' and b.operatetype = 'mark' \n" +
                        "          ) union all select ID  from MPMPROJECT a\n" +
                        "where exists(select 1 from MPMPROJECTTASK b inner join BaseOperateRecord c on  b.id = c.SOURCEID and c.SOURCETYPE = 'MpmProjectTask' and c.usercode = '{0}' and c.operatetype = 'mark' WHERE  a.projectcode = b.projectcode))", this.getUserBean().getUser().getCode());
            } else {
                sql.appendSingle("and MPMPROJECT.id not in (select ID from MPMPROJECT a\n" +
                        "where exists ( select 1 from BaseOperateRecord b  where a.ID = b.SOURCEID and b.SOURCETYPE = 'MpmProject' and b.usercode = '{0}' and b.operatetype = 'mark'\n" +
                        "          ) union all select ID  from MPMPROJECT a\n" +
                        "where exists(select 1 from MPMPROJECTTASK b inner join BaseOperateRecord c on  b.id = c.SOURCEID and c.SOURCETYPE = 'MpmProjectTask' and c.usercode = '{0}' and c.operatetype = 'mark' WHERE  a.projectcode = b.projectcode))", this.getUserBean().getUser().getCode());
            }
        }
        // 系统部支持筛选
        if (paraMap.containsKey("qry_needsupport")) {
            String value = paraMap.get("qry_needsupport").toString();
            if (!StringUtil.isEmpty(value)) {
                List<Map<String, Object>> mpmSystemTasknameList = datacodeCache.getCodeListMap("MpmSystemTaskname", "", "sortcode");
                String taskName = mpmSystemTasknameList.stream().map(item -> "'" + ConvertUtil.convertToString(item.get("name")) + "'").collect(Collectors.joining(","));
                sql.appendSingle(" and exists (select 1 from mpmprojecttask task where task.projectcode = mpmproject.projectcode and needsupport = '{0}' and task.state = 9 and {1} is not null and {2} in ({3})) ", value, MpmProjectTaskEntity.FieldPost, MpmProjectTaskEntity.FieldTaskname, taskName);
            }
        }
        // 采购模式筛选条件
        if (paraMap.containsKey("qry_purchasepattern")) {
            String value = ConvertUtil.convertToString(paraMap.get("qry_purchasepattern"));
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle(" and exists (select 1 from mpmprojectsale where mpmproject.id = mpmprojectsale.id and purchasepattern in ({0})) ", value);
            }
        }
        // 春季/秋季攻势筛选条件
        if (paraMap.containsKey("qry_projectattack")) {
            String value = ConvertUtil.convertToString(paraMap.get("qry_projectattack"));
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle(" and {0} in ({1}) ", SQLAdapter.isNull(MpmProjectEntity.tableName + "." + MpmProjectEntity.FieldProjectattack, "0"), value);
            }
        }
        // 是否进入短名单筛选条件
        if (paraMap.containsKey("qry_intonamelist")) {
            String value = ConvertUtil.convertToString(paraMap.get("qry_intonamelist"));
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle(" and exists (select 1 from mpmprojecttask where mpmproject.projectcode = mpmprojecttask.projectcode and mpmprojecttask.state = 9 and dmdtask = 1 and intonamelist = '{0}') ", value);
            }
        }
        if (paraMap.containsKey("qry_where")) {
            String value = paraMap.get("qry_where").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and {0}", value);
        }
        // 管理员、超级管理员，不控制权限
        if (userBean.getUser().getAdminlevel() < 1) {
            String value = this.getUserBean().getUser().getCode();
            sql.appendSingle(" and (EXISTS (SELECT PROJECTCODE FROM MPMPROJECTSTAKEHOLDER STACKHOLDER WHERE STACKHOLDER.enabled = '1' and STACKHOLDER.USERCODE = '{0}' AND {1}.{2} = STACKHOLDER.PROJECTCODE) or mpmproject.projectmanagercode = '{0}' or mpmproject.usercode = '{0}' ",
                    value, MpmProjectEntity.tableName, MpmProjectEntity.FieldProjectcode);
            // TODO: 2022/8/30 duzehou 根据用户管辖省区查找项目功能
            //获取用户code
            String userCode = userBean.getUser().getCode();
            //根绝用户Code在deptExtraInfo表中找到自己作为省总或者财总的省份
            List<String> deptCommonCodes = mmsDeptExtraInfoService.getDeptCommonCodes(userCode);
            //将List<String> 转换为 String 以便于拼接SQL
            String commonCodes = StringUtils.join(deptCommonCodes.toArray(), ",");
            //根据commonCodes匹配Project
            if (!ObjectUtil.isEmpty(commonCodes)){
                sql.appendSingle(" OR mpmproject.deptcommoncode IN ({0})", commonCodes);
            }
            String dataPession = mpmProjectService.getDataPermission(TapSystemConst.TapOperationTypeEnum.Select, "", userBean);
            if (StringUtil.isEmpty(dataPession)) {
                sql.appendSingle(" ) ");
            } else {
                sql.appendSingle(" or {0}) ", dataPession);
            }
        }
        return sql.toString();
    }


    /**
     * @Title: setDispFields
     * @description:设置字段显示值
     * @param mapRow
     * @author: lanjianwei
     * @date: 2020年12月30日下午2:31:17
     * @modify:
     */
    private void setDispFields(Map<String, Object> mapRow) {
        String temp;
        if (mapRow.get("existbidscore") != null) {
            temp = mapRow.get("existbidscore").toString();
            mapRow.put("dispexistbidscore", datacodeCache.getValueDynamic("CboYesNo", temp, "code", "name"));
        }
        if (mapRow.get("flowid") != null) {
            temp = mapRow.get("flowid").toString();
            mapRow.put("dispflowid", datacodeCache.getValueDynamic("ApprovalFlow_MpmProject", temp, "id", "name"));
        }
        if (mapRow.get("state") != null) {
            temp = mapRow.get("state").toString();
            mapRow.put("dispstate", datacodeCache.getValueDynamic("TapApprovalBillState", temp, "code", "name"));
        }
        if (mapRow.get("enabled") != null) {
            temp = mapRow.get("enabled").toString();
            mapRow.put("dispenabled", datacodeCache.getValueDynamic("CboYesNo", temp, "code", "name"));
        }
        if (mapRow.get("projectlevel") != null) {
            temp = mapRow.get("projectlevel").toString();
            mapRow.put("dispprojectlevel", datacodeCache.getValueDynamic("MpmProjectLevel", temp, "code", "name"));
        }
        // 如果单据为审批不通过，项目状态显示立项不通过
        if (mapRow.get("projectstate") != null) {
            mapRow.put("dispprojectstate", getDispprojectstate(mapRow));
        }
        if (mapRow.get("deptname1") != null) {
            String temp1 = ConvertUtil.convertToString(mapRow.get("deptname1"));
            String temp2 = ConvertUtil.convertToString(mapRow.get("deptname2"));
            String temp3 = ConvertUtil.convertToString(mapRow.get("deptname3"));
            String dispdeptname = this.mmsProvinceCityMatchinfoService.getDispdeptname(temp1, temp2, temp3);
            mapRow.put("dispdeptname", dispdeptname);
        } else {
            mapRow.put("dispdeptname", "");
        }
        // 解析自评打分编码
        if (mapRow.get("bidscorestate") != null) {
            temp = mapRow.get("bidscorestate").toString();
            mapRow.put("dispbidscorestate", datacodeCache.getValueDynamic("MpmBidScoreState", temp, "code", "name"));
        }
        // 解析采购模式
        if (mapRow.get("purchasepattern") != null) {
            temp = mapRow.get("purchasepattern").toString();
            mapRow.put("disppurchasepattern", datacodeCache.getValueDynamic("MpmPurchasePattern", temp, "code", "name"));
        }
        // 自评状态
        if (mapRow.get("firstbidscore") != null) {
            temp = mapRow.get("firstbidscore").toString();
            String name = datacodeCache.getValueDynamic("MpmProjectBidScoreState", temp, "code", "name");
            mapRow.put("dispfirstbidscore", StringUtil.isEmpty(name) ? temp : name);
        }
        if (mapRow.get("secondbidscore") != null) {
            temp = mapRow.get("secondbidscore").toString();
            String name = datacodeCache.getValueDynamic("MpmProjectBidScoreState", temp, "code", "name");
            mapRow.put("dispsecondbidscore", StringUtil.isEmpty(name) ? temp : name);
        }
        if (mapRow.get("uploaddocument") != null) {
            temp = mapRow.get("uploaddocument").toString();
            mapRow.put("dispuploaddocument", datacodeCache.getValueDynamic("CboYesNo", temp, "code", "name"));
        }
        if (mapRow.get("pmoprojecttype") != null) {
            temp = mapRow.get("pmoprojecttype").toString();
            mapRow.put("disppmoprojecttype", datacodeCache.getValueDynamic("MpmPMOProjectType", temp, "code", "name"));
        }
        // 如果会议开始时间存在，计算应开周会，会议结束时间为当前日期上一周与存入值的最小日期
        if (mapRow.get("meetbegindate") != null) {
            Date begindate = ConvertUtil.convertToDate(mapRow.get("meetbegindate"));
            Date enddate = mpmProjectService.getMeetPreviousDate(ConvertUtil.convertToDate(mapRow.get("meetenddate")));
            if (enddate.compareTo(begindate) < 0) {
                mapRow.put("meetneed", 0);
            } else {
                StringBufferProxy sql = new StringBufferProxy();
                sql.appendSingle(" enabled = 1 ");
                List<MpmProjectNoMeetWeekEntity> mpmProjectNoMeetWeekEntities = mpmProjectNoMeetWeekService.queryByWhere(sql.toString());
                int count = 0;
                for (MpmProjectNoMeetWeekEntity weekEntity : mpmProjectNoMeetWeekEntities) {
                    if (weekEntity.getMeetweekdate().compareTo(begindate) >= 0 && weekEntity.getMeetweekdate().compareTo(enddate) <= 0) {
                        count++;
                    }
                }
                mapRow.put("meetneed", (enddate.getTime() - begindate.getTime()) / (7 * 24 * 60 * 60 * 1000L) - count + 1);
            }
        }
    }

    /**
     * @author liuzhenfei
     * @Description 格式化页面项目状态
     * @Date 2021/6/30 17:46
     * @Param [mapRow]
     * @return java.lang.String
     **/
    public String getDispprojectstate(Map<String, Object> mapRow) {
        String result = "";
        String state = mapRow.get("projectstate").toString();
        //如果项目状态为 1 “进行中”，那么，页面显示的项目状态采用 进度状态 显示
        //老数据该字段会出现null情况，那么状态值采用项目状态
        String schedulestate = mapRow.get("schedulestate") != null ? mapRow.get("schedulestate").toString() : state;
        state = "1".equals(state) ? schedulestate : state;
        result = datacodeCache.getValueDynamic("MpmProjectState", state, "code", "name");

        //当进度状态是 ：执行阶段 时，还需后缀 未完成的里程序号/里程总数
        //ps：未完成的里程序号 : 按照计划达成时间正序查询,判断未达成里程的序号
        if ("7".equals(schedulestate)) {
            StringBufferProxy sql = new StringBufferProxy();
            sql.appendSingle("projectcode = '{0}' and enabled=1 order by sortcode asc, planfinishtime asc, id asc", mapRow.get("projectcode"));
            //查询该项目下的里程碑及任务
            List<MpmProjectMilepostEntity> mpmProjectMilepostEntityList = mpmProjectMilepostService.queryByWhere(sql.toString());
            //未完成的里程序号
            int reachCount = 0;
            for (int i = 0; i < mpmProjectMilepostEntityList.size(); i++) {
                MpmProjectMilepostEntity mpmProjectMilepostEntity = mpmProjectMilepostEntityList.get(i);
                //判断里程状态，满足条件跳出循环
                if (mpmProjectMilepostEntity.getMilepoststate() == 0) {
                    reachCount = i + 1;
                    break;
                }
            }
            //重新定义项目状态
            sql.clear();
            // 若里程碑及任务都已完成则显示“复盘阶段”
            sql.appendSingle("{0}{1}/{2}", result, reachCount, mpmProjectMilepostEntityList.size());
            result = sql.toString();
        }
        return result;
    }

    /**
     * @author: wudaliang
     * @date: 2021-03-09
     * @description: 获取当前登录用户
     * @return: com.topscomm.tap.common.SessionUserBean
     * @modify:
     */
    public SessionUserBean getUserBean() {
        return (SessionUserBean) ThreadLocalContext.sessionUserBeanThreadLocal.get();
    }


    /**
     * @author liuzhenfei
     * @Description
     * @Date 2021/7/3 10:59
     * @Param [paraMap]
     * @return com.topscomm.pub.vo.ResponseResult<java.lang.Object>
     **/
    public ResponseResult<Object> insertForProjectChange(Map<String, Object> paraMap) {
        // 具体思路： 页面传输的参数中，带有后缀：ForChange 的字段是变更字段。根据后缀判断切割，就可以获取对应真实字段，继续后面操作
        Map<String, Object> dataMap = paraMap;
        System.out.println(JSONObject.toJSONString(paraMap));
        Map<String, Object> dataMapTemp = new HashMap<String, Object>();
        Map<String, Object> dataMaps = new HashMap<String, Object>();
        return ResponseResult.ok(dataMaps);
    }

    /**
     * @description: 项目查询模块，列表数据权限sql组装
     * @param: paraMap
     * @param: tableName
     * @param: columnName
     * @return: java.lang.String
     * @author Administrator
     * @date: 2021/1/15 9:35
     */
    public String existsSqlForQuery(Map<String, Object> paraMap, String tableName, String columnName) {
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle(" {0}.enabled = 1 ", tableName, columnName);
        if (paraMap.containsKey("qry_finishproject")) {
            String value = paraMap.get("qry_finishproject").toString();
            if (!StringUtil.isEmpty(value) && "0".equals(value)) {
                sql.appendSingle(" and nvl(projectstate, 0) in ('{0}','{1}','{2}') ", MpmSystemConst.ProjectState.StartProjectCode, MpmSystemConst.ProjectState.ExecuteStateCode, MpmSystemConst.ProjectState.UnstartProjectCode);
            }
        }
        // 根据督办情况筛选
        if (paraMap.containsKey("qry_supervisestate")) {
            String value = paraMap.get("qry_supervisestate").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle("and (");
                // 若有选择无督办筛选条件则先执行（3为无督办对应编号）
                if (value.contains("3")) {
                    sql.appendSingle("(NOT EXISTS (SELECT ID FROM MPMPROJECTSUPERVISE supervise WHERE" +
                            " supervise.PROJECTCODE = mpmproject.PROJECTCODE))");
                    // 若除了选择无督办筛选情况还选择了其他选项，则要把字符串进行截取。
                    // 因为无督办情况代表督办表无当前项目的督办记录，其他选项都是有督办记录的。
                    // 如下截取无需遍历字符串，提高代码效率
                    if (value.length() > 1) {
                        sql.appendSingle(" or ");
                        // 因为字符串排列顺序不同，若无督办情况不是第一个选中的选项则进行第一种截取（例如"0,1,3", "2,3,1"等情况）
                        // 若第一个选中的是无督办情况则进行第二种截取。主要区别在于逗号的位置不同。（例如"3,1,0"等情况）
                        if (value.indexOf("3") != 0) {
                            value = value.replace(",3", "");
                        } else {
                            value = value.replace("3,", "");
                        }
                    }
                }
                // 筛选进行中、超期、已完成三个状态的督办情况
                if (!value.contains("3")) {
                    sql.appendSingle("(EXISTS (SELECT ID FROM MPMPROJECTSUPERVISE supervise WHERE" +
                            " supervise.PROJECTCODE = mpmproject.PROJECTCODE AND supervise.SUPERVISESTATE IN ({0})))", value);
                }
                sql.appendSingle(")");
            }
        }
        if (paraMap.containsKey("startDateForFinisthDate")) {
            String value = paraMap.get("startDateForFinisthDate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.finishdate> {0} ", SQLAdapter.toDateShort(value));
        }
        if (paraMap.containsKey("endDaterFinisthDate")) {
            String value = paraMap.get("endDaterFinisthDate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.finishdate< {0} ", SQLAdapter.toDateShort(value) + " + 1");
        }
        if (paraMap.containsKey("startDateForActualEndDate")) {
            String value = paraMap.get("startDateForActualEndDate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.actualenddate> {0} ", SQLAdapter.toDateShort(value));
        }
        if (paraMap.containsKey("query_createdepart")) {
            String value = paraMap.get("query_createdepart").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.deptname  in ({0})", value);
        }
        if (paraMap.containsKey("qry_owner")) {
            String value = paraMap.get("qry_owner").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.owner like '%{0}%' ", value);
        }
        if (paraMap.containsKey("qry_sponsor")) {
            String value = paraMap.get("qry_sponsor").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.sponsor like '%{0}%' ", value);
        }
        if (paraMap.containsKey("qry_bidcycle")) {
            String value = paraMap.get("qry_bidcycle").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.bidcycle  in ('{0}')", value);
        }
        if (paraMap.containsKey("qry_abnormalstate")) {
            String value = paraMap.get("qry_abnormalstate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.abnormalstate  in ({0})", value);
        }
        if (paraMap.containsKey("qry_decisionusername")) {
            String value = paraMap.get("qry_decisionusername").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and EXISTS (SELECT ID FROM MPMPROJECTTEAM WHERE mpmproject.PROJECTCODE = MPMPROJECTTEAM.PROJECTCODE AND (MPMPROJECTTEAM.USERCODE like '%{0}%' OR MPMPROJECTTEAM.USERNAME like '%{0}%') AND MPMPROJECTTEAM.STATE = 9  AND MPMPROJECTTEAM.ENABLED = 1) ", value);
        }
        if (paraMap.containsKey("qry_projectstate")) {
            String value = paraMap.get("qry_projectstate").toString();
            if (!StringUtil.isEmpty(value)) {
                //因为该查询是两个字段合并查询，所以需要重新解析页面传值
                //解析页面传值，将数字全部去出
                Pattern pattern = Pattern.compile("[^0-9]");
                Matcher matcher = pattern.matcher(value);
                String temp = matcher.replaceAll("");
                StringBufferProxy projectstateValue = new StringBufferProxy();
                StringBufferProxy schedulestateValue = new StringBufferProxy();
                for (int i = 0; i < temp.length(); i++) {
                    String status = temp.substring(i, i + 1);
                    switch (status) {
                        case "0":
                        case "1":
                        case "2":
                        case "3":
                        case "4":
                        case "8":
                            projectstateValue.appendSingle("'{0}',", status);
                            break;
                        case "5":
                        case "6":
                        case "7":
                        case "9":
                            schedulestateValue.appendSingle("'{0}',", status);
                            break;
                    }
                }

                if (StringUtils.isNotBlank(projectstateValue.toString()) && StringUtils.isBlank(schedulestateValue.toString())) {
                    sql.appendSingle(" and mpmproject.projectstate  in ({0})  ",
                            projectstateValue.substring(0, projectstateValue.length() - 1));
                } else if (StringUtils.isBlank(projectstateValue.toString()) && StringUtils.isNotBlank(schedulestateValue.toString())) {
                    sql.appendSingle(" and mpmproject.schedulestate  in ({0})  ",
                            schedulestateValue.substring(0, schedulestateValue.length() - 1));
                } else {
                    sql.appendSingle(" and (mpmproject.projectstate  in ({0}) or mpmproject.schedulestate  in ({1}) ) ",
                            projectstateValue.substring(0, projectstateValue.length() - 1),
                            schedulestateValue.substring(0, schedulestateValue.length() - 1));
                }

            }
        }
        if (paraMap.containsKey("qry_projectcode")) {
            String value = paraMap.get("qry_projectcode").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.projectcode like '%{0}%' ", value);
        }
        if (paraMap.containsKey("qry_projectlevel")) {
            String value = paraMap.get("qry_projectlevel").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.projectlevel  in ({0})", value);
        }
        if (paraMap.containsKey("endDateForActualEndDate")) {
            String value = paraMap.get("endDateForActualEndDate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.actualenddate< {0} ", SQLAdapter.toDateShort(value));
        }
        if (paraMap.containsKey("qry_projectname")) {
            String value = paraMap.get("qry_projectname").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and (mpmproject.projectname like '%{0}%' or mpmproject.projectcode like '%{0}%') ", value);
        }
        if (paraMap.containsKey("qry_open")) {
            String value = paraMap.get("qry_open").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.open = 1", value);
        }
        if (paraMap.containsKey("qry_deptcommoncode")) {
            String value = paraMap.get("qry_deptcommoncode").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.deptcommoncode in ({0}) ", value);
        }
        if (paraMap.containsKey("qry_projectmanager")) {
            String value = paraMap.get("qry_projectmanager").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and (mpmproject.projectmanager like '%{0}%' or mpmproject.projectmanagercode like '%{0}%') ", value);
        }
        if (paraMap.containsKey("qry_projectsource")) {
            String value = paraMap.get("qry_projectsource").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and exists (select 1 from mpmprojectsale where mpmproject.id = mpmprojectsale.id and projectsource in ({0}))", value);
        }
        if (paraMap.containsKey("qry_endtype")) {
            String value = paraMap.get("qry_endtype").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle("and mpmproject.endtype in ({0})", value);
            }
        }
        if (paraMap.containsKey("qry_pmoprojecttype")) {
            String value = paraMap.get("qry_pmoprojecttype").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle("and mpmproject.pmoprojecttype in ({0})", value);
            }
        }
        // 挂网日期筛选
        if (paraMap.containsKey("startDateForBidBeginDate") && paraMap.containsKey("endDateForBidBeginDate")) {
            String startDateForBidBeginDate = paraMap.get("startDateForBidBeginDate").toString(),
                    endDateForBidBeginDate = paraMap.get("endDateForBidBeginDate").toString();
            sql.appendSingle(" and exists (select 1 from mpmprojectsale where mpmproject.id = mpmprojectsale.id and bidbegindate between {0} and {1}) ", SQLAdapter.toDateShort(startDateForBidBeginDate), SQLAdapter.toDateShort(endDateForBidBeginDate));

        }
        // 开标日期筛选
        if (paraMap.containsKey("startDateForBidEndDate") && paraMap.containsKey("endDateForBidEndDate")) {
            String startDateForBidEndDate = paraMap.get("startDateForBidEndDate").toString(),
                    endDateForBidEndDate = paraMap.get("endDateForBidEndDate").toString();
            sql.appendSingle(" and exists (select 1 from mpmprojectsale where mpmproject.id = mpmprojectsale.id and bidenddate between {0} and {1}) ", SQLAdapter.toDateShort(startDateForBidEndDate), SQLAdapter.toDateShort(endDateForBidEndDate));
        }
        if (paraMap.containsKey("qry_dataPermission")) {
            String value = ConvertUtil.convertToString(paraMap.get("qry_dataPermission"));
            if ("inChargeMe".equals(value)) {
                // 我负责的（项目经理是我）
                sql.appendSingle(" and mpmproject.PROJECTMANAGERCODE='{0}' ", this.getUserBean().getUser().getCode());
            } else if ("participate".equals(value)) {
                // 我参与的（项目组成员中有我）
                sql.appendSingle(" and EXISTS (SELECT PROJECTCODE FROM MPMPROJECTTEAM MPMPROJECTTEAM WHERE mpmproject.PROJECTCODE = MPMPROJECTTEAM.PROJECTCODE AND MPMPROJECTTEAM.USERCODE = '{0}' AND MPMPROJECTTEAM.STATE = 9  AND MPMPROJECTTEAM.ENABLED = 1) ", this.getUserBean().getUser().getCode());
            }
        }
        if (paraMap.containsKey("qry_purchasepattern")) {
            String value = ConvertUtil.convertToString(paraMap.get("qry_purchasepattern"));
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle(" and exists (select 1 from mpmprojectsale where mpmproject.id = mpmprojectsale.id and purchasepattern in ({0})) ", value);
            }
        }
        // 春季/秋季攻势筛选条件
        if (paraMap.containsKey("qry_projectattack")) {
            String value = ConvertUtil.convertToString(paraMap.get("qry_projectattack"));
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle(" and {0} in ({1}) ", SQLAdapter.isNull(MpmProjectEntity.tableName + "." + MpmProjectEntity.FieldProjectattack, "0"), value);
            }
        }
        // 项目结果筛选条件
        if (paraMap.containsKey("qry_projectresult")) {
            String value = ConvertUtil.convertToString(paraMap.get("qry_projectresult"));
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle(" and exists (select 1 from mpmprojectreview where mpmproject.projectcode = mpmprojectreview.projectcode and projectresult in ({0}) and mpmprojectreview.state = 9) ", value);
            }
        }
        // 如果是通过hession远程调用则没有登录用户，手动拼接用户信息
        SessionUserBean sessionUserBean = null;
        if (paraMap.containsKey("noLoginUser")) {
            SecurityUserDetails userDetails = (SecurityUserDetails) userDetailsService.loadUserByUsername(ConvertUtil
                    .convertToString(paraMap.get("usercodefromrpc")));
            sessionUserBean = new SessionUserBean();
            sessionUserBean.setUser(userDetails.getUser());
            sessionUserBean.setOrg(userDetails.getOrg());
            sessionUserBean.setWorkOrg(userDetails.getWorkOrg());
            sessionUserBean.setDept(userDetails.getDept());
            sessionUserBean.setWorkDept(userDetails.getWorkDept());
            sessionUserBean.setRoleList(userDetails.getRoleList());
        } else {
            sessionUserBean = this.getUserBean();
        }
        // 项目监管页面只开放给经营管理部同事，数据权限为全部项目
        return sql.toString();
    }

    /**
     * @author: wudaliang
     * @date: 2021-04-08
     * @description: 判断当前登录人是否为部门领导(一级部门 、 二级部门.....)
     * @return: java.lang.Boolean
     * @modify:
     */
    public Boolean isDeptHeader(long id) {
        List<CboDeptEntity> cboDeptEntities = cboDeptService
                .queryByWhere("HEADERIDS='" + id + "' and ENABLED=1");
        if (cboDeptEntities.size() > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * @author: wudaliang
     * @date: 2021-04-08
     * @param: id
     * @description: 获取自己为部门长的部门编码
     * @return: java.lang.String
     * @modify:
     */
    public String[] getHeaderDeptCode(long id) {
        List<CboDeptEntity> cboDeptEntities = cboDeptService
                .queryByWhere("HEADERIDS='" + id + "' and ENABLED=1");
        String[] strings = new String[cboDeptEntities.size()];
        for (int i = 0; i < cboDeptEntities.size(); i++) {
            strings[i] = cboDeptEntities.get(i).getCode();
        }
        return strings;
    }

    /**
     * @Title: 获取任命超期/任命待办数据
     * @description:
     * @param paraMap
     * @return
     * @author: houyuhang
     * @date: 2021年11月08日
     * @modify: 2021年11月24日
     */
    public Page queryAppointmentOverDate(@RequestParam(required = false) Map<String, Object> paraMap, String whereSql) {
        paraMap = BaseUtil.decodeSecureMap(paraMap);
        Query query = new Query(paraMap);
        Map<String, Object> tempMap = new HashMap<>();
        if (ConvertUtil.convertToInt(paraMap.get("qry_overdue")) == 1) {
            // 获取4天之前的日期
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            String overdueAppointmentTime = MpmSystemConst.overdueAppointmentTime;
            int time = Integer.parseInt(overdueAppointmentTime) * (-1);
            calendar.add(Calendar.DATE, time);
            Date overdueDate = calendar.getTime();
            String overdueTime = DateUtil.dateToString(overdueDate, "yyyy-MM-dd");
            tempMap.put("overdue", 1);
            tempMap.put("overduetime", overdueTime);
        }
        int currentPage = query.getCurrentPage();
        int pageSize = query.getPageSize();
        int begincount = currentPage * pageSize - pageSize;
        tempMap.put("sidx", query.getSidx());
        tempMap.put("sord", query.getSord());
        tempMap.put("pagesize", pageSize);
        tempMap.put("begincount", begincount);
        tempMap.put("whereSql", whereSql);
        // 查询项目总数
        int totalCount = mpmProjectService.getCount(tempMap, "queryAppointmentOverdue");
        List<Map<String, Object>> tempList = null;
        if (query.getPageSize() > 0) {
            tempList = mpmProjectService.queryMapForPage(tempMap, "queryAppointmentOverduePage");
        } else {
            tempList = mpmProjectService.queryMapByWhere(whereSql, "queryAppointmentOverdue");
        }
        List<Map<String, Object>> resultList = new ArrayList<>();
        for (Map<String, Object> mapRow : tempList) {
            setDispFields(mapRow);
            resultList.add(mapRow);
        }
        // 单据状态
        for (Map<String, Object> temp : tempList) {
            if (ConvertUtil.convertToString(temp.get("dispstate")).isEmpty()) {
                temp.put("dispstate", "未创建");
            }
        }
        // 超期天数
        String overdueTime = MpmSystemConst.overdueAppointmentTime;
        overdueDatetime(tempList, overdueTime);
        Page page = new Page(resultList, totalCount, query.getPageSize(), query.getCurrentPage());
        return page;
    }

    /**
     * @Title: 获取策划超期/策划待办数据
     * @description:
     * @param paraMap
     * @return
     * @author: houyuhang
     * @date: 2021年11月08日
     * @modify: 2021年11月24日
     */
    public Page queryPlanOverDate(@RequestParam(required = false) Map<String, Object> paraMap, String whereSql) {
        paraMap = BaseUtil.decodeSecureMap(paraMap);
        Query query = new Query(paraMap);
        StringBufferProxy sql = new StringBufferProxy();
        Map<String, Object> tempMap = new HashMap<>();
        if (ConvertUtil.convertToInt(paraMap.get("qry_overdue")) == 1) {
            // 获取7天之前的日期
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            String overduePlanTime = MpmSystemConst.overduePlanTime;
            int time = Integer.parseInt(overduePlanTime) * (-1);
            calendar.add(Calendar.DATE, time);
            Date overdueDate = calendar.getTime();
            String overdueTime = DateUtil.dateToString(overdueDate, "yyyy-MM-dd");
            tempMap.put("overdue", 1);
            tempMap.put("overduetime", overdueTime);
        }
        int currentPage = query.getCurrentPage();
        int pageSize = query.getPageSize();
        int begincount = currentPage * pageSize - pageSize;
        tempMap.put("sidx", query.getSidx());
        tempMap.put("sord", query.getSord());
        tempMap.put("pagesize", pageSize);
        tempMap.put("begincount", begincount);
        tempMap.put("whereSql", whereSql);
        // 查询项目总数
        int totalCount = mpmProjectService.getCount(tempMap, "queryPlanOverdue");
        List<Map<String, Object>> tempList = null;
        if (query.getPageSize() > 0) {
            tempList = mpmProjectService.queryMapForPage(tempMap, "queryPlanOverduePage");
        } else
            tempList = mpmProjectService.queryMapByWhere(whereSql, "queryPlanOverdue");
        List<Map<String, Object>> resultList = new ArrayList<>();
        for (Map<String, Object> mapRow : tempList) {
            setDispFields(mapRow);
            resultList.add(mapRow);
        }
        // 单据状态
        for (Map<String, Object> temp : tempList) {
            if (ConvertUtil.convertToString(temp.get("dispstate")).isEmpty()) {
                temp.put("dispstate", "未创建");
            }
        }
        // 超期天数
        String overdueTime = MpmSystemConst.overduePlanTime;
        overdueDatetime(tempList, overdueTime);
        Page page = new Page(resultList, totalCount, query.getPageSize(), query.getCurrentPage());
        return page;
    }

    /**
     * @Title: 返回所有项目编号作为查询条件
     * @description:
     * @param tempList
     * @return
     * @author: zhangjunyao
     * @date: 2021年11月26日
     * @modify:
     */
    private String getProjectCodes(List<Map<String, Object>> tempList) {
        List<String> codeList = new ArrayList<String>();
        for (Map<String, Object> mapRow : tempList) {
            String projectCode = "'" + ConvertUtil.convertToString(mapRow.get("projectcode")) + "'";
            codeList.add(projectCode);
        }
        return String.join(",", codeList);
    }

    /**
     * @Title: 获取立项、任命、策划的审批开始、结束时间
     * @description:
     * @param
     * @return
     * @author: zhangjunyao
     * @date: 2021年11月19日
     * @modify:
     */
    private void getVariousDateForSuperVise(List<Map<String, Object>> tempList) {
        StringBufferProxy sql = new StringBufferProxy();
        String sourceIds = tempList.stream().map(map -> ConvertUtil.convertToString(map.get("id")))
                .collect(Collectors.joining("','", "'", "'"));
        String projectCodes = tempList.stream().map(map -> ConvertUtil.convertToString(map.get("projectcode")))
                .collect(Collectors.joining("','", "'", "'"));
        Map<String, List<Map<String, Object>>> resultMap;
        // 查询立项开始时间
        sql.appendSingle(" {0} in ({1}) and {2} = '{3}' and {4} = '{5}' order by {6} ", CboApprovalNodeInsEntity.FieldSourceid, sourceIds,
                CboApprovalNodeInsEntity.FieldSourcetype, MpmProjectEntity.tableName, CboApprovalNodeInsEntity.FieldExtendfield2,
                MpmSystemConst.ApprovalState.Begin, CboApprovalNodeInsEntity.FieldApprovaldate);
        List<CboApprovalNodeInsEntity> nodeInsEntities = cboApprovalNodeInsService.queryByWhere(sql.toString());
        // 封装立项开始时间
        setVariousDate(tempList, nodeInsEntities, MpmProjectEntity.tableName, null, "submitbegindate");
        // 查询立项结束时间
        sql.clear();
        sql.appendSingle(" {0} in ({1}) and {2} = '{3}' and {4} = '{5}' ", CboApprovalNodeInsEntity.FieldSourceid, sourceIds,
                CboApprovalNodeInsEntity.FieldSourcetype, MpmProjectEntity.tableName, CboApprovalNodeInsEntity.FieldExtendfield2,
                MpmSystemConst.ApprovalState.End);
        nodeInsEntities = cboApprovalNodeInsService.queryByWhere(sql.toString());
        // 封装立项结束时间
        setVariousDate(tempList, nodeInsEntities, MpmProjectEntity.tableName, null, "submitfinishdate");
        // 查询任命开始时间
        sql.clear();
        sql.appendSingle(" {0} in ({1}) order by createon ", MpmProjectAppointmentEntity.FieldProjectcode, projectCodes);
        List<Map<String, Object>> appointmentList = mpmProjectAppointmentService.queryMapByWhere(sql.toString());
        sourceIds = appointmentList.stream().map(map -> ConvertUtil.convertToString(map.get("id")))
                .collect(Collectors.joining("','", "'", "'"));
        resultMap = appointmentList.stream().collect(Collectors.groupingBy(map -> ConvertUtil.convertToString(map.get("projectcode"))));
        sql.clear();
        sql.appendSingle(" {0} in ({1}) and {2} = '{3}' and {4} = '{5}' order by {6} ", CboApprovalNodeInsEntity.FieldSourceid, sourceIds,
                CboApprovalNodeInsEntity.FieldSourcetype, MpmProjectAppointmentEntity.tableName, CboApprovalNodeInsEntity.FieldExtendfield2,
                MpmSystemConst.ApprovalState.Begin, CboApprovalNodeInsEntity.FieldApprovaldate);
        nodeInsEntities = cboApprovalNodeInsService.queryByWhere(sql.toString());
        // 封装任命开始时间
        setVariousDate(tempList, nodeInsEntities, MpmProjectAppointmentEntity.tableName, resultMap, "appointbegindate");
        // 查询任命结束时间
        sql.clear();
        sql.appendSingle(" {0} in ({1}) and {2} = '{3}' and {4} = '{5}' ", CboApprovalNodeInsEntity.FieldSourceid, sourceIds,
                CboApprovalNodeInsEntity.FieldSourcetype, MpmProjectAppointmentEntity.tableName, CboApprovalNodeInsEntity.FieldExtendfield2,
                MpmSystemConst.ApprovalState.End);
        nodeInsEntities = cboApprovalNodeInsService.queryByWhere(sql.toString());
        // 封装任命结束时间
        setVariousDate(tempList, nodeInsEntities, MpmProjectAppointmentEntity.tableName, resultMap, "appointfinishdate");
        // 查询策划开始时间
        sql.clear();
        sql.appendSingle(" {0} in ({1}) and {2} = 'V1.0' and state in ('1','9') ", MpmProjectPlanEntity.FieldProjectcode, projectCodes, MpmProjectPlanEntity.FieldVersion);
        List<Map<String, Object>> planList = mpmProjectPlanService.queryMapByWhere(sql.toString());
        sourceIds = planList.stream().map(map -> ConvertUtil.convertToString(map.get("id")))
                .collect(Collectors.joining("','", "'", "'"));
        resultMap = planList.stream().collect(Collectors.groupingBy(map -> ConvertUtil.convertToString(map.get("projectcode"))));
        sql.clear();
        sql.appendSingle(" {0} in ({1}) and {2} = '{3}' and {4} = '{5}' order by {6} ", CboApprovalNodeInsEntity.FieldSourceid, sourceIds,
                CboApprovalNodeInsEntity.FieldSourcetype, MpmProjectPlanEntity.tableName, CboApprovalNodeInsEntity.FieldExtendfield2,
                MpmSystemConst.ApprovalState.Begin, CboApprovalNodeInsEntity.FieldApprovaldate);
        nodeInsEntities = cboApprovalNodeInsService.queryByWhere(sql.toString());
        // 封装策划开始时间
        setVariousDate(tempList, nodeInsEntities, MpmProjectPlanEntity.tableName, resultMap, "planbegindate");
        // 查询策划结束时间
        sql.clear();
        sql.appendSingle(" {0} in ({1}) and {2} = '{3}' and {4} = '{5}' ", CboApprovalNodeInsEntity.FieldSourceid, sourceIds,
                CboApprovalNodeInsEntity.FieldSourcetype, MpmProjectPlanEntity.tableName, CboApprovalNodeInsEntity.FieldExtendfield2,
                MpmSystemConst.ApprovalState.End);
        nodeInsEntities = cboApprovalNodeInsService.queryByWhere(sql.toString());
        // 封装策划结束时间
        setVariousDate(tempList, nodeInsEntities, MpmProjectPlanEntity.tableName, resultMap, "planfinishdate");
    }

    /**
     * @Title: 封装立项、任命、策划的审批开始、结束时间
     * @description:
     * @param
     * @return
     * @author: zhangjunyao
     * @date: 2021年11月26日
     * @modify:
     */
    private void setVariousDate(List<Map<String, Object>> tempList, List<CboApprovalNodeInsEntity> nodeInsEntities,
                                String tableName, Map<String, List<Map<String, Object>>> resultMap, String dateName) {
        if (nodeInsEntities.isEmpty()) {
            return;
        }
        // 转化为以sourceid区分的时间map
        Map<String, List<CboApprovalNodeInsEntity>> nodeInsMap = nodeInsEntities.stream().collect(Collectors
                .groupingBy(map -> ConvertUtil.convertToString(map.getSourceid())));
        for (Map<String, Object> mapRow : tempList) {
            // 若是立项则id从templist中获取，否则从resultmap中获取
            String projectCode = ConvertUtil.convertToString(mapRow.get("projectcode")), id = "";
            if (tableName.equals(MpmProjectEntity.tableName)) {
                id = ConvertUtil.convertToString(mapRow.get("id"));
            } else {
                if (resultMap.containsKey(projectCode)) {
                    id = ConvertUtil.convertToString(resultMap.get(projectCode).get(0).get("id"));
                }
            }
            if (nodeInsMap.containsKey(id)) {
                // 因为只显示第一次任命/策划的时间，所以直接选择第一个
                CboApprovalNodeInsEntity nodeInsEntity = nodeInsMap.get(id).get(0);
                mapRow.put(dateName, nodeInsEntity.getApprovaldate());
            }
        }
    }

    /**
     * @Title: 获取当前里程碑及任务
     * @description:
     * @param tempList
     * @return
     * @author: zhangjunyao
     * @date: 2021年11月19日
     * @modify:
     */
    public void setCurrentMilePost(List<Map<String, Object>> tempList) {
        String projectCodes = tempList.stream().map(map -> ConvertUtil.convertToString(map.get("projectcode")))
                .collect(Collectors.joining("','", "'", "'"));
        StringBufferProxy sql = new StringBufferProxy();
        // 获取处于未达成状态的、创建时间最早的里程碑及任务
        sql.appendSingle("projectcode in ({0}) and {1} = 0 and enabled = 1 order by {2} desc", projectCodes,
                MpmProjectMilepostEntity.FieldMilepoststate, MpmProjectMilepostEntity.FieldPlanfinishtime);
        List<MpmProjectMilepostEntity> milepostEntities = mpmProjectMilepostService.queryByWhere(sql.toString());
        if (CollectionUtils.isEmpty(milepostEntities)) {
            for (Map<String, Object> mapRow : tempList) {
                mapRow.put("currentmilepost", "暂无未达成的里程碑及任务");
            }
            return;
        }
        Map<String, List<MpmProjectMilepostEntity>> milepostMap = milepostEntities.stream().collect(Collectors.groupingBy(
                entity -> entity.getProjectcode()));
        for (Map<String, Object> mapRow : tempList) {
            String projectCode = ConvertUtil.convertToString(mapRow.get("projectcode"));
            if (milepostMap.containsKey(projectCode)) {
                mapRow.put("currentmilepost", milepostMap.get(projectCode).get(0).getMilepostname());
            } else {
                mapRow.put("currentmilepost", "暂无未达成的里程碑及任务");
            }
        }
    }

    /**
     * @Title: 列表督办过滤处理
     * @description:
     * @param tempList
     * @return
     * @author: heyiheng
     * @date: 2023年02月28日
     * @modify:
     */
    public List<Map<String, Object>> handleSuperviseListForList(List<Map<String, Object>> tempList) {
        // 系统自动发起的，如果销售PMO审批退回或者未审核通过，不显示
        List<Map<String, Object>> resultList = tempList.stream().filter(item -> !(
                ("1".equals(ConvertUtil.convertToString(item.get("sendresource")))
                        && "2".equals(ConvertUtil.convertToString(item.get("state"))) && "审批不通过,已退回".equals(item.get("latestphase")))
               || ("1".equals(ConvertUtil.convertToString(item.get("sendresource")))
                        && "1".equals(ConvertUtil.convertToString(item.get("state"))) && "PMO审核".equals(item.get("latestphase")))
        )).collect(Collectors.toList());
        // 添加督办单状态
//        Date date = DateUtil.getNowDate();
//        for (Map map : resultList ) {
//            if (map.get("superviseendstate") != null) {
//                map.put("displiststate",  map.get("dispsuperviseendstate"));
//            } else {
//                // 如果是历史数据
//                if ("9".equals(ConvertUtil.convertToString(map.get("state")))) {
//                    map.put("dispListstate", map.get("dispsupervisestate"));
//                } else {
//                    // 未审批关闭数据
//                    Date superviseDate = (Date) map.get("superviseperiod");
//                    if(date.after(superviseDate)) {
//                        map.put("displiststate", "超期");
//                    } else {
//                        map.put("displiststate", "进行中");
//                    }
//                }
//            }
//        }
        return resultList;
    }
    /**
     * @Title: 项目监管页面获取各项目督办情况(项目监管页面加载用 ）
     * @description:
     * @param tempList
     * @return
     * @author: zhangjunyao
     * @date: 2021年11月26日
     * @modify:
     */
    private void setSuperviseListForList(List<Map<String, Object>> tempList, String projectCodes) {
        StringBufferProxy sql = new StringBufferProxy();
        // 查询督办表获取所有项目的督办情况
        sql.appendSingle("projectcode in ({0})", projectCodes);
        List<Map<String, Object>> superviseMap = mpmProjectSuperviseService.queryMapByWhere(sql.toString());
        // 处理结果
        superviseMap = handleSuperviseListForList(superviseMap);
        if (!superviseMap.isEmpty()) {
            // 转为以projectcode区分的督办list
            Map<String, List<Map<String, Object>>> dataMap = superviseMap.stream()
                    .collect(Collectors.groupingBy(map -> ConvertUtil.convertToString(map.get("projectcode"))));
            for (Map<String, Object> mapRow : tempList) {
                mapRow.put("superviselist", dataMap.get(ConvertUtil.convertToString(mapRow.get("projectcode"))));
            }
        }
    }

    /**
     * @Title: 项目监管页面获取各项目督办情况（项目监管页面导出用）
     * @description:
     * @param tempList
     * @return
     * @author: zhangjunyao
     * @date: 2021年12月4日
     * @modify:
     */
    private void setSuperviseListForExcel(List<Map<String, Object>> tempList, String projectCodes) {
        StringBufferProxy sql = new StringBufferProxy();
        // 查询督办表获取所有项目的督办情况
        sql.appendSingle("projectcode in ({0})", projectCodes);
        List<Map<String, Object>> superviseMap = mpmProjectSuperviseService.queryMapByWhere(sql.toString());
        if (!superviseMap.isEmpty()) {
            // 转为以projectcode区分的督办list
            Map<String, List<Map<String, Object>>> dataMap = superviseMap.stream()
                    .collect(Collectors.groupingBy(map -> ConvertUtil.convertToString(map.get("projectcode"))));
            for (Map<String, Object> mapRow : tempList) {
                List<Map<String, Object>> dataList = dataMap.get(ConvertUtil.convertToString(mapRow.get("projectcode")));
                String superviseList = "";
                if (!CollectionUtils.isEmpty(dataList)) {
                    List<String> superviseStr = new ArrayList<String>();
                    for (Map<String, Object> supervise : dataList) {
                        String str = ConvertUtil.convertToString(supervise.get(MpmProjectSuperviseEntity.FieldSupervisecode)) + " " +
                                supervise.get("displiststate");
                        superviseStr.add(str);
                    }
                    superviseList = String.join("\n", superviseStr);
                }
                mapRow.put("superviselist", superviseList);
            }
        }
    }

    /**
     * @Title: 项目监管页面导出机会点字段处理
     * @description:
     * @param tempList
     * @return
     * @author: zhangjunyao
     * @date: 2021年12月4日
     * @modify:
     */
    private void setChanceClueForExcel(List<Map<String, Object>> tempList) {
        for (Map<String, Object> mapRow : tempList) {
            String chanceClues = ConvertUtil.convertToString(mapRow.get("chanceclue"));
            if (chanceClues.contains(",")) {
                chanceClues = chanceClues.replace(",", "\n");
                mapRow.put("chanceclue", chanceClues);
            }
        }
    }

    /**
     * 计算超期天数
     * @param tempList
     * @param overdueTime
     * @return
     */
    void overdueDatetime(List<Map<String, Object>> tempList, String overdueTime) {
        for (Map<String, Object> temp : tempList) {
            BigDecimal time = ConvertUtil.convertToBigDecimal(temp.get("daynum"));
            temp.put("daynum", time.subtract(ConvertUtil.convertToBigDecimal(overdueTime)));
        }
    }

    /**
     * @Title: 项目监管查询所有挂网、开标日期
     * @description:
     * @param tempList
     * @return
     * @author: zhangjunyao
     * @date: 2022年2月14日
     * @modify:
     */
    private void getBidDateForList(List<Map<String, Object>> tempList) throws ParseException {
        String ids = tempList.stream().map(map -> ConvertUtil.convertToString(map.get("id")))
                .collect(Collectors.joining("','", "'", "'"));
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle(" projectid in ({0}) ", ids);
        List<Map<String, Object>> bidDateList = mpmProjectBidDateService.queryMapByWhere(sql.toString());
        if (CollectionUtil.isEmpty(bidDateList)) {
            return;
        }
        // 对日期进行格式处理
        SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd");
        Date date;
        String dateStr = "";
        for (Map<String, Object> dateMap : bidDateList) {
            dateStr = ConvertUtil.convertToString(dateMap.get(MpmProjectBidDateEntity.FieldProjectbidbegindate));
            if (!StringUtil.isEmpty(dateStr)) {
                date = s.parse(dateStr);
                dateStr = s.format(date);
                dateMap.put(MpmProjectBidDateEntity.FieldProjectbidbegindate, dateStr);
            }
            dateStr = ConvertUtil.convertToString(dateMap.get(MpmProjectBidDateEntity.FieldProjectbidenddate));
            if (!StringUtil.isEmpty(dateStr)) {
                date = s.parse(dateStr);
                dateStr = s.format(date);
                dateMap.put(MpmProjectBidDateEntity.FieldProjectbidenddate, dateStr);
            }
        }
        Map<String, List<Map<String, Object>>> bidDateMap = bidDateList.stream().collect(Collectors.groupingBy(
                map -> ConvertUtil.convertToString(map.get("projectid"))));
        for (Map<String, Object> mapRow : tempList) {
            String id = ConvertUtil.convertToString(mapRow.get("id"));
            if (bidDateMap.containsKey(id)) {
                mapRow.put("projectbiddate", bidDateMap.get(id));
            }
        }
    }

    /**
     * @Title: 项目监管查询所有挂网、开标日期（导出用）
     * @description:
     * @param tempList
     * @return
     * @author: zhangjunyao
     * @date: 2022年2月15日
     * @modify:
     */
    private void getBidDateForExcel(List<Map<String, Object>> tempList) throws ParseException {
        String ids = tempList.stream().map(map -> ConvertUtil.convertToString(map.get("id")))
                .collect(Collectors.joining("','", "'", "'"));
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle(" projectid in ({0}) ", ids);
        List<Map<String, Object>> bidDateList = mpmProjectBidDateService.queryMapByWhere(sql.toString());
        if (CollectionUtil.isEmpty(bidDateList)) {
            return;
        }
        // 对日期进行格式处理
        SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd");
        Date date;
        String dateStr = "";

        for (Map<String, Object> dateMap : bidDateList) {
            dateStr = ConvertUtil.convertToString(dateMap.get(MpmProjectBidDateEntity.FieldProjectbidbegindate));
            if (!StringUtil.isEmpty(dateStr)) {
                date = s.parse(dateStr);
                dateStr = s.format(date);
                dateMap.put(MpmProjectBidDateEntity.FieldProjectbidbegindate, dateStr);
            }
            dateStr = ConvertUtil.convertToString(dateMap.get(MpmProjectBidDateEntity.FieldProjectbidenddate));
            if (!StringUtil.isEmpty(dateStr)) {
                date = s.parse(dateStr);
                dateStr = s.format(date);
                dateMap.put(MpmProjectBidDateEntity.FieldProjectbidenddate, dateStr);
            }
        }
        Map<String, List<Map<String, Object>>> bidDateMap = bidDateList.stream().collect(Collectors.groupingBy(
                map -> ConvertUtil.convertToString(map.get("projectid"))));
        for (Map<String, Object> mapRow : tempList) {
            String id = ConvertUtil.convertToString(mapRow.get("id"));
            if (bidDateMap.containsKey(id)) {
                List<Map<String, Object>> dateList = bidDateMap.get(id);
                String bidBeginDates = dateList.stream().map(map -> ConvertUtil.convertToString(map.get(MpmProjectBidDateEntity.FieldProjectbidbegindate)))
                        .collect(Collectors.joining("\n"));
                String bidEndDates = dateList.stream().map(map -> ConvertUtil.convertToString(map.get(MpmProjectBidDateEntity.FieldProjectbidenddate)))
                        .collect(Collectors.joining("\n"));
                mapRow.put(MpmProjectBidDateEntity.FieldProjectbidbegindate, bidBeginDates);
                mapRow.put(MpmProjectBidDateEntity.FieldProjectbidenddate, bidEndDates);
            }
        }
    }

    /**
     * @Title: list
     * @description:系统部监管模块list方法
     * @param paraMap, whereSql
     * @return
     * @author: zhangjunyao
     * @date: 2022年2月18日
     * @modify:
     */
    public Page listForSys(Map<String, Object> paraMap, String sql) throws Exception {
        paraMap = BaseUtil.decodeSecureMap(paraMap);
        Query query = new Query(paraMap);
        Map<String, Object> tempMap = new HashMap<>();
        int currentPage = query.getCurrentPage();
        int pageSize = query.getPageSize();
        int begincount = currentPage * pageSize - pageSize;
        tempMap.put("sidx", query.getSidx());
        tempMap.put("sord", query.getSord());
        tempMap.put("pagesize", pageSize);
        tempMap.put("begincount", begincount);
        tempMap.put("whereSql", sql);
        // 查询项目总数
        int totalCount = mpmProjectService.getCount1(tempMap, "queryMapByWhereForSys");
        List<Map<String, Object>> tempList;
        if (query.getPageSize() > 0 && !paraMap.containsKey("notQueryForPageFlag")) {
            tempList = mpmProjectService.queryMapForPage(tempMap, "queryMapForPageForSys");
        } else
            tempList = mpmProjectService.queryMapByWhere(sql, "queryMapByWhereForSys");
        if (CollectionUtils.isEmpty(tempList)) {
            return new Page(tempList, totalCount, query.getPageSize(), query.getCurrentPage());
        }
        // 查询是否关注
        tempList = baseOperateRecordService.getMarkedData(tempList, MpmProjectEntity.tableName, getUserBean().getUser().getCode());
        // 项目监管查询所有挂网、开标日期
        // getBidDateForList(tempList);
        // 查询A级项目是否存在新客户决策分析
        getCustomerTaskForList(tempList, paraMap);
        List<Map<String, Object>> resultList = new ArrayList<>();
        for (Map<String, Object> mapRow : tempList) {
            setDispFields(mapRow);
            resultList.add(mapRow);
        }
        return new Page(resultList, totalCount, query.getPageSize(), query.getCurrentPage());
    }

    /**
     * @Title: list
     * @description:查询A级项目是否存在新客户决策分析
     * @author: houyuhang
     * @date: 2022年03月18日
     * @modify:
     */
    private void getCustomerTaskForList(List<Map<String, Object>> tempList, Map<String, Object> paraMap) {
        // 获取客户决策分析
        String projectcode = tempList.stream().map(item -> "'" + ConvertUtil.convertToString(item.get("projectcode")) + "'").collect(Collectors.joining(","));
        if (StringUtil.isEmpty(projectcode)) {
            return;
        }
        StringBufferProxy sql = new StringBufferProxy();
        if (paraMap.containsKey("qry_needsupport") && !ObjectUtil.isEmpty(paraMap.get("qry_needsupport"))) {
            String needsupport = ConvertUtil.convertToString(paraMap.get("qry_needsupport"));
            sql.appendSingle(" {0} = '{1}' and ", MpmProjectTaskEntity.FieldNeedsupport, needsupport);
        }
        List<Map<String, Object>> mpmSystemTasknameList = datacodeCache.getCodeListMap("MpmSystemTaskname", "", "sortcode");
        String taskName = mpmSystemTasknameList.stream().map(item -> "'" + ConvertUtil.convertToString(item.get("name")) + "'").collect(Collectors.joining(","));
        sql.appendSingle(" projectcode in ({0}) and state = 9 and {1} is not null and {2} in ({3}) ", projectcode, MpmProjectTaskEntity.FieldPost, MpmProjectTaskEntity.FieldTaskname, taskName);
        List<MpmProjectTaskEntity> customerTaskList = mpmProjectTaskService.queryByWhere(sql.toString());
        // 客户决策分析按照projectcode分类
        Map<String, List<MpmProjectTaskEntity>> customerTaskMap = customerTaskList.stream().collect(Collectors.groupingBy(MpmProjectTaskEntity::getProjectcode));
        for (Map<String, Object> temp : tempList) {
            List<MpmProjectTaskEntity> res = customerTaskMap.get(ConvertUtil.convertToString(temp.get("projectcode")));
            if (CollectionUtil.isEmpty(res)) {
                temp.put("disabledexpand", true);
            } else {
                temp.put("disabledexpand", false);
            }
        }
    }

    /**
     * @Title: list
     * @description:项目监管需要返回项目/里程碑及任务/任务超期时间、次数
     * @author: houyuhang
     * @date: 2022年05月06日
     * @modify:
     */
    private void getOverdueTimeAndNum(List<Map<String, Object>> tempList) {
        Map<String, Object> dataMap = new HashMap<>();
        // 项目超期按项目编号分组
        List<Map<String, Object>> overdueList = mpmProjectOverdueRecordService.queryMap(dataMap, "getOverdueTimeAndNum");
        Map<String, List<Map<String, Object>>> overdueMap = overdueList.stream().collect(Collectors.groupingBy(item -> ConvertUtil.convertToString(item.get("projectcode"))));
        for (Map<String, Object> tempMap : tempList) {
            List<Map<String, Object>> overdue = overdueMap.get(ConvertUtil.convertToString(tempMap.get("projectcode")));
            if (CollectionUtil.isEmpty(overdue)) {
                continue;
            }
            tempMap.putAll(overdue.get(0));
        }
    }

    /**
     * @Title: list
     * @description:返回内部产品信息
     * @author: houyuhang
     * @date: 2022年05月12日
     * @modify:
     */
    private void addProductLine(Map<String, Object> dataMap) {
        List<Map> productRangeList = JSONArray.parseArray(ConvertUtil.convertToString(dataMap.get("productrange")), Map.class);
        if (CollectionUtil.isEmpty(productRangeList) || ObjectUtil.isEmpty(productRangeList.get(0))) {
            return;
        }
        for (Map productRange : productRangeList) {
            if (productRange.containsKey("productLine")) {
                continue;
            }
            StringBufferProxy sql = new StringBufferProxy();
            sql.appendSingle(" projectcode = '{0}' and productname = '{1}' and fund = '{2}' ", dataMap.get("projectcode"), productRange.get("productType"), productRange.get("productMoney"));
            MpmProjectProductInfoEntity mpmProjectProductInfoEntity = mpmProjectProductInfoService.queryObjectByWhere(sql.toString());
            if (ObjectUtil.isEmpty(mpmProjectProductInfoEntity)) {
                continue;
            }
            productRange.put("productLine", mpmProjectProductInfoEntity.getProductlinename());
        }
        String productRangeJson = JSONArray.toJSONString(productRangeList);
        dataMap.put("productrange", productRangeJson);
    }
    /**
     * @Title: list
     * @description:返回内部产品信息
     * @author: houyuhang
     * @date: 2022年05月12日
     * @modify:
     */
    public void getClientName(List<Map<String, Object>> tempList) {
        StringBufferProxy sql = new StringBufferProxy();
        String ids = tempList.stream().map(item -> "'" + ConvertUtil.convertToString(item.get("id")) + "'").collect(Collectors.joining(","));
        sql.appendSingle(" sourceid in ({0}) and sourcetype = 'MpmProject' ", ids);
        List<MpmProjectCustomerEntity> mpmProjectCustomerEntities = mpmProjectCustomerService.queryByWhere(sql.toString());
        Map<String, List<MpmProjectCustomerEntity>> mpmProjectCustomerEntitiesMap = mpmProjectCustomerEntities.stream().collect(Collectors.groupingBy(item -> ConvertUtil.convertToString(item.getSourceid())));
        for (Map<String, Object> temp : tempList) {
            List<MpmProjectCustomerEntity> mpmProjectCustomerEntities1 = mpmProjectCustomerEntitiesMap.get(ConvertUtil.convertToString(temp.get("id")));
            if (CollectionUtils.isEmpty(mpmProjectCustomerEntities1)) {
                continue;
            }
            List<String> customerEntityList = mpmProjectCustomerEntities1.stream().map(MpmProjectCustomerEntity::getClientname).collect(Collectors.toList());
            temp.put("customerlist", customerEntityList);
        }
    }
    /**
     * @description: 导出任务明细excel 返回excel所需字段
     * @author: duzehou
     * @date:  17:25
     * @param: [paraMap]
     * @return: java.util.List<com.topscomm.mpm.vo.MpmProjectDetailsVO>
     **/
    public List<MpmProjectTaskDetailsVO> exportTaskDetailExcel(Map<String, Object> paraMap, SessionUserBean userBean) {
        Query query = new Query(paraMap);
        //获取用户权限可以查看的条件sql
        String whereSql = this.spellListSqlForTrack(paraMap, userBean);
        //根绝sql查询符合条件的project和sale聚合物
        Query query1 = new Query(paraMap);
        Map<String, Object> tempMap1 = new HashMap<>();
        tempMap1.put("sidx", query1.getSidx());
        tempMap1.put("sord", query1.getSord());
        tempMap1.put("whereSql", whereSql);
        List<Map<String, Object>> projectSaleDTOList = mpmProjectService.queryMapForPage(tempMap1, "queryPolymarizationMapByWhereOrder");
        //从ListMap中获取projectcodeList
        List<String> codeList = projectSaleDTOList.stream().map(DTO->ConvertUtil.convertToString(DTO.get(MpmProjectEntity.FieldProjectcode))).collect(Collectors.toList());
        //查询结果过是否为空，为空则返回空list
        if (ObjectUtil.isEmpty(projectSaleDTOList)) {
            return new ArrayList<>();
        }
        StringBufferProxy taskSql = new StringBufferProxy();
        //查询MpmSystemTaskname 拼接taskSql
        List<Map<String,Object>> items = datacodeCache.getCodeListMap("MpmSystemTaskname","","sortcode");
        String itemNames = items.stream().map(m->"'"+ConvertUtil.convertToString(m.get("name"))+"'").collect(Collectors.joining(","));
        //便于拼接SQL 將codeList转换为String
        String codeListStr = codeList.stream().map(code -> "'" + code + "'").collect(Collectors.joining(","));

        taskSql.appendSingle( " PROJECTCODE IN ({0}) AND TASKNAME IN ({1}) and state = 9 and post is not null ",codeListStr,itemNames);
        StringBufferProxy scoreSql = new StringBufferProxy();
        scoreSql.appendSingle( " PROJECTCODE IN ({0}) AND STATE = 9 ",codeListStr);
        //实体类转Map
        if(ObjectUtil.isEmpty(projectSaleDTOList)){
            projectSaleDTOList = new ArrayList<>();
        }
//        Map<String, Map<String, Object>> projectSaleMap = new HashMap<>();
        Map<String, Map<String, Object>> projectSaleMap = projectSaleDTOList
                .stream()
                .collect(Collectors.toMap(map -> ConvertUtil.convertToString(map.get(MpmProjectEntity.FieldProjectcode)), map -> map, (key1,key2)->key1));
        List<MpmProjectTaskEntity> mpmProjectTaskEntities = mpmProjectTaskService.queryByWhere(ConvertUtil.convertToString(taskSql));
        if (ObjectUtil.isEmpty(mpmProjectTaskEntities)){
            mpmProjectTaskEntities = new ArrayList<>();
        }
        Map<String, MpmProjectTaskEntity> taskEntityMap = new HashMap<>();
        for (MpmProjectTaskEntity mpmProjectTaskEntity : mpmProjectTaskEntities) {
            taskEntityMap.put(mpmProjectTaskEntity.getProjectcode()+ "-" + mpmProjectTaskEntity.getTaskname(),mpmProjectTaskEntity);
        }
//                mpmProjectTaskEntities
//                .stream()
//                .collect(Collectors.toMap(mpmProjectTaskEntity -> mpmProjectTaskEntity.getProjectcode() + "-" + mpmProjectTaskEntity.getTaskname(), value2  -> value2));
        scoreSql.clear();
        scoreSql.appendSingle(" select projectcode,times,type,sum(selfscore) as sumscore from mpmprojectbidscore where projectcode in ({0}) and state = 9 group by projectcode,times,type ", codeListStr);
        List<Map<String, Object>> scoreEntityList = mpmProjectBidScoreService.selectExecute(scoreSql.toString());
        Map<String, List<Map<String, Object>>> scoreEntityMap = scoreEntityList.stream().collect(Collectors.groupingBy(item -> ConvertUtil.convertToString(item.get("projectcode"))));

//        Map<String, MpmProjectBidScoreEntity> scoreEntityMap = mpmProjectBidScoreEntities
//                .stream()
//                .collect(Collectors.toMap(mpmProjectBidScoreEntity -> mpmProjectBidScoreEntity.getProjectcode() + "-" + mpmProjectBidScoreEntity.getTimes() + "-" + mpmProjectBidScoreEntity.getType(), score -> score));

        //根据code将各表数据添加到VO
        //新建返回类型的VOList
        List<MpmProjectTaskDetailsVO> VOList = new ArrayList<>();

        //循环向VOList中添加vo
        for (String code : codeList) {
            MpmProjectTaskDetailsVO vo = new MpmProjectTaskDetailsVO();
            this.setDispFields(projectSaleMap.get(code));
            //部门名称
            vo.setDeptname(ConvertUtil.convertToString(projectSaleMap.get(code).get(MpmProjectEntity.FieldDeptname)));
            //项目名称
            vo.setProjectname(ConvertUtil.convertToString(projectSaleMap.get(code).get(MpmProjectEntity.FieldProjectname)));
            //参与金额
            vo.setParticipatemoney(ConvertUtil.convertToBigDecimal(projectSaleMap.get(code).get(MpmProjectSaleEntity.FieldParticipatemoney)));
            //开标日期
            if (ObjectUtil.isEmpty(projectSaleMap.get(code).get(MpmProjectSaleEntity.FieldBidbegindate))){
                vo.setBidbegindate("");
            }
            String hhmmss = ConvertUtil.convertToString(projectSaleMap.get(code).get(MpmProjectSaleEntity.FieldBidbegindate));
            String dateStr = hhmmss.replace("00:00:00.0", "");
            vo.setBidbegindate(dateStr);
            //项目级别
            vo.setProjectlevel(ConvertUtil.convertToString(projectSaleMap.get(code).get("dispprojectlevel")));

            //项目阶段
            vo.setProjectstate(ConvertUtil.convertToString(projectSaleMap.get(code).get("dispprojectstate")));
            //结项金额
            vo.setProjectendmoney(ConvertUtil.convertToBigDecimal(projectSaleMap.get(code).get(MpmProjectEntity.FieldProjectendmoney)));

            //引导技术规范
            String jsgf = code + "-" + MpmSystemConst.MpmSystemTaskname.technicalSpecifications;
            if (taskEntityMap.get(jsgf)!=null){
                vo.setJsgfContent(ConvertUtil.convertToString(taskEntityMap.get(jsgf).getTaskdetail()));
                vo.setJsgfCompletion(Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(jsgf).getTaskstate()), "0")
                        || Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(jsgf).getTaskstate()), "1") ? "未完成":"已完成");
            }
            //引导评标细则
            String pbxz = code + "-" + MpmSystemConst.MpmSystemTaskname.detailForBidEvaluation;
            if (taskEntityMap.get(pbxz)!=null){
                vo.setPbxzContent(ConvertUtil.convertToString(taskEntityMap.get(pbxz).getTaskdetail()));
                vo.setPbxzCompletion(Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(pbxz).getTaskstate()), "0")
                        || Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(pbxz).getTaskstate()), "1") ? "未完成":"已完成");
            }
            //引导检测标准
            String jcbz = code + "-" + MpmSystemConst.MpmSystemTaskname.testStandard;
            if (taskEntityMap.get(jcbz)!=null){
                vo.setJcbzContent(taskEntityMap.get(jcbz).getTaskdetail());
                vo.setJcbzCompletion(Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(jcbz).getTaskstate()), "0")
                        || Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(jcbz).getTaskstate()), "1") ? "未完成":"已完成");
            }
            //引导最高限价
            String zgxj = code + "-" + MpmSystemConst.MpmSystemTaskname.ceilingPrice;
            if (taskEntityMap.get(zgxj)!=null){
                vo.setZgxjContent(ConvertUtil.convertToString(taskEntityMap.get(zgxj).getTaskdetail()));
                vo.setZgxjCompletion(Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(zgxj).getTaskstate()), "0")
                        || Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(zgxj).getTaskstate()), "1") ? "未完成":"已完成");
            }
            //引导分标分包
            String fbfb = code + "-" + MpmSystemConst.MpmSystemTaskname.subContracting;
            if (taskEntityMap.get(fbfb)!=null){
                vo.setFbfbContent(ConvertUtil.convertToString(taskEntityMap.get(fbfb).getTaskdetail()));
                vo.setFbfbCompletion(Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(fbfb).getTaskstate()), "0")
                        || Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(fbfb).getTaskstate()), "1") ? "未完成":"已完成");
            }
            //引导跟进评标决策客户
            String pbjckh = code + "-" + MpmSystemConst.MpmSystemTaskname.bidEvaluationDecisionCustomer;
            if (taskEntityMap.get(pbjckh)!=null){
                vo.setPbjckhContent(ConvertUtil.convertToString(taskEntityMap.get(pbjckh).getTaskdetail()));
                vo.setPbjckhCompletion(Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(pbjckh).getTaskstate()), "0")
                        || Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(pbjckh).getTaskstate()), "1") ? "未完成":"已完成");
            }
            //引导跟进定标会决策客户
            String dbhjckh = code + "-" + MpmSystemConst.MpmSystemTaskname.calibrationMeetingCustomer;
            if (taskEntityMap.get(dbhjckh)!=null){
                vo.setDbhjckhContent(ConvertUtil.convertToString(taskEntityMap.get(dbhjckh).getTaskdetail()));
                vo.setDbhjckhCompletion(Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(dbhjckh).getTaskstate()), "0")
                        || Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(dbhjckh).getTaskstate()), "1") ? "未完成":"已完成");
            }
            //引导接触业务部门主任
            String cd = code + "-" + MpmSystemConst.MpmSystemTaskname.businessDepartmentDirector;
            if (taskEntityMap.get(cd)!=null){
                vo.setCdContent(ConvertUtil.convertToString(taskEntityMap.get(cd).getTaskdetail()));
                vo.setCdCompletion(Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(cd).getTaskstate()), "0")
                        || Objects.equals(ConvertUtil.convertToString(taskEntityMap.get(cd).getTaskstate()), "1") ? "未完成":"已完成");
            }
            //评分细则
            List<Map<String, Object>> scoreList = scoreEntityMap.get(code);
            if (!CollectionUtil.isEmpty(scoreList)) {
                BigDecimal[] selfScoreArr = new BigDecimal[4];
                Arrays.fill(selfScoreArr, BigDecimal.ZERO);
                for (Map<String, Object> scoreMap : scoreList) {
                    BigDecimal selfScore = ConvertUtil.convertToBigDecimal(scoreMap.get("sumscore"));
                    if ("1".equals(scoreMap.get("times")) && "1".equals(scoreMap.get("type"))) {
                        selfScoreArr[0] = selfScore;
                    } else if ("1".equals(scoreMap.get("times")) && "2".equals(scoreMap.get("type"))) {
                        selfScoreArr[1] = selfScore;
                    } else if ("2".equals(scoreMap.get("times")) && "1".equals(scoreMap.get("type"))) {
                        selfScoreArr[2] = selfScore;
                    } else {
                        selfScoreArr[3] = selfScore;
                    }
                }
                if (selfScoreArr[0].compareTo(BigDecimal.ZERO)!=0 || selfScoreArr[1].compareTo(BigDecimal.ZERO)!=0) {
                    vo.setFir(selfScoreArr[0] + "/" + selfScoreArr[1]);
                }
                if (selfScoreArr[2].compareTo(BigDecimal.ZERO)!=0 || selfScoreArr[3].compareTo(BigDecimal.ZERO)!=0) {
                    vo.setSec(selfScoreArr[2] + "/" + selfScoreArr[3]);
                }
            }
            //将vo添加到VOList中
            VOList.add(vo);
        }
        return VOList;
    }
    /**
     * @Title: list
     * @description:获取进入短名单信息
     * @author: houyuhang
     * @date: 2022年11月08日
     * @modify:
     */
    public void queryDmdInfoForList(List<Map<String, Object>> tempList) {
        StringBufferProxy sql = new StringBufferProxy();
        String projectcode = tempList.stream().map(item -> "'" + ConvertUtil.convertToString(item.get("projectcode")) + "'").collect(Collectors.joining(","));
        sql.appendSingle(" enabled = 1 and state = 9 and projectcode in ({0}) and dmdtask = 1 ", projectcode);
        List<MpmProjectTaskEntity> mpmProjectTaskEntities = mpmProjectTaskService.queryByWhere(sql.toString());
        Map<String, List<MpmProjectTaskEntity>> mpmProjectTaskEntitiesMap = mpmProjectTaskEntities.stream().collect(Collectors.groupingBy(MpmProjectTaskEntity::getProjectcode));
        for (Map<String, Object> temp : tempList) {
            List<MpmProjectTaskEntity> taskEntities = mpmProjectTaskEntitiesMap.get(ConvertUtil.convertToString(temp.get("projectcode")));
            if (CollectionUtils.isEmpty(taskEntities)) {
                temp.put("dispintonamelist", "/");
                continue;
            }
            temp.put("dispintonamelist", datacodeCache.getValueDynamic("MpmIntoNameList", taskEntities.get(0).getIntonamelist(), "code", "name"));
        }
    }
    /**
     * @description:获取项目复盘相关信息
     * @author: houyuhang
     * @date: 2022年12月12日
     * @modify:
     */
    public void getProjectReviewResult(List<Map<String, Object>> tempList) {
        String projectcode = tempList.stream().map(item -> "'" + ConvertUtil.convertToString(item.get("projectcode")) + "'").collect(Collectors.joining(","));
        if (StringUtil.isEmpty(projectcode)) {
            return;
        }
        // 获取项目对应的复盘信息
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle(" enabled = 1 and projectcode in ({0}) and state = 9 ", projectcode);
        List<MpmProjectReviewEntity> mpmProjectReviewEntities = mpmProjectReviewService.queryByWhere(sql.toString());
        Map<String, List<MpmProjectReviewEntity>> reviewMap = mpmProjectReviewEntities.stream().collect(Collectors.groupingBy(MpmProjectReviewEntity::getProjectcode));
        for (Map<String, Object> tempMap : tempList) {
            if (CollectionUtil.isEmpty(reviewMap.get(tempMap.get("projectcode")))) {
                continue;
            }
            MpmProjectReviewEntity tempReview = reviewMap.get(tempMap.get("projectcode")).get(0);
            tempMap.put("dispprojectresult", datacodeCache.getValueDynamic("MpmProjectResult", tempReview.getProjectresult(), "code", "name"));
            tempMap.put("resulttime", tempReview.getResulttime());
            tempMap.put("settlementmoney", tempReview.getSettlementmoney());
        }
    }
    /**
     * @description:获取项目风险信息
     * @author: houyuhang
     * @date: 2022年12月12日
     * @modify:
     */
    public void getProjectRiskInfo(List<Map<String, Object>> tempList) {
        String projectcode = tempList.stream().map(item -> "'" + ConvertUtil.convertToString(item.get("projectcode")) + "'").collect(Collectors.joining(","));
        if (StringUtil.isEmpty(projectcode)) {
            return;
        }
        // 获取项目对应的风险信息
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle(" enabled = 1 and projectcode in ({0}) and state = 9 ", projectcode);
        List<MpmProjectRiskEntity> mpmProjectRiskAll = mpmProjectRiskService.queryByWhere(sql.toString());
        Map<String, List<MpmProjectRiskEntity>> riskMap = mpmProjectRiskAll.stream().collect(Collectors.groupingBy(MpmProjectRiskEntity::getProjectcode));
        for (Map<String, Object> tempMap : tempList) {
            if (CollectionUtil.isEmpty(riskMap.get(tempMap.get("projectcode")))) {
                continue;
            }
            int allNum = riskMap.get(tempMap.get("projectcode")).size();
            // 未闭环风险数量（风险状态不为关闭的情况）
            int actualNum = riskMap.get(tempMap.get("projectcode")).stream().filter(item -> !MpmSystemConst.RiskStatus.RiskStatusClose.equals(item.getRiskstatus())).collect(Collectors.toList()).size();
            tempMap.put("risknum", actualNum + "/" + allNum);
        }
    }
    /**
     * @description:获取项目任务信息
     * @author: houyuhang
     * @date: 2022年12月13日
     * @modify:
     */
    public void queryProjectTaskInfo(List<Map<String, Object>> tempList) {
        String projectcode = tempList.stream().map(item -> "'" + ConvertUtil.convertToString(item.get("projectcode")) + "'").collect(Collectors.joining(","));
        if (StringUtil.isEmpty(projectcode)) {
            return;
        }
        // 获取项目任务信息
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle(" enabled = 1 and dmdtask in (1,2,4) and projectcode in ({0}) ", projectcode);
        List<MpmProjectTaskEntity> mpmProjectTaskEntities = mpmProjectTaskService.queryByWhere(sql.toString());
        Map<String, List<MpmProjectTaskEntity>> taskMap = mpmProjectTaskEntities.stream().collect(Collectors.groupingBy(MpmProjectTaskEntity::getProjectcode));
        for (Map<String, Object> tempMap : tempList) {
            if (CollectionUtil.isEmpty(taskMap.get(tempMap.get("projectcode")))) {
                continue;
            }
            List<MpmProjectTaskEntity> tempTask = taskMap.get(tempMap.get("projectcode"));
            // dmdtask=1的任务
            List<MpmProjectTaskEntity> tempTask1 = tempTask.stream().filter(item -> item.getDmdtask() == 1).collect(Collectors.toList());
            if (!CollectionUtil.isEmpty(tempTask1)) {
                tempMap.put("disphavenamelist", datacodeCache.getValueDynamic("CboYesNo", tempTask1.get(0).getHavenamelist(), "code", "name"));
                tempMap.put("dispintonamelist", datacodeCache.getValueDynamic("MpmIntoNameList", tempTask1.get(0).getIntonamelist(), "code", "name"));
            }
            // dmdtask=2的任务
            List<MpmProjectTaskEntity> tempTask2 = tempTask.stream().filter(item -> item.getDmdtask() == 2).collect(Collectors.toList());
            if (!CollectionUtil.isEmpty(tempTask2)) {
                this.getGuoTask(tempTask2, tempMap);
            }
            // dmdtask=4的任务
            List<MpmProjectTaskEntity> tempTask4 = tempTask.stream().filter(item -> item.getDmdtask() == 4).collect(Collectors.toList());
            if (!CollectionUtil.isEmpty(tempTask4)) {
                this.getNanTask(tempTask4, tempMap);
            }
        }
    }
    /**
     * @description:获取国网履约评价/运行绩效任务
     * @author: houyuhang
     * @date: 2022年12月14日
     * @modify:
     */
    public void getGuoTask(List<MpmProjectTaskEntity> tempTask2, Map<String, Object> tempMap) {
        StringBuffer stringBuffer = new StringBuffer();
        MpmProjectTaskEntity taskEntity = tempTask2.get(0);
        if (!StringUtil.isEmpty(taskEntity.getGuoscore())) {
            stringBuffer.append("供应商绩效评价得分" + taskEntity.getGuoscore() + "、");
        }
        if (!StringUtil.isEmpty(taskEntity.getGuorank())) {
            stringBuffer.append("供应商绩效评价排名" + taskEntity.getGuorank() + "、");
        }
        if (!StringUtil.isEmpty(taskEntity.getGuoposition())) {
            stringBuffer.append("供应商绩效评价档位" + taskEntity.getGuoposition() + "、");
        }
        String guoTask = stringBuffer.toString().substring(0, stringBuffer.length() > 0 ? stringBuffer.length() - 1 : 0);
        tempMap.put("movetask", guoTask);
    }
    /**
     * @description:获取国网履约评价/运行绩效任务
     * @author: houyuhang
     * @date: 2022年12月14日
     * @modify:
     */
    public void getNanTask(List<MpmProjectTaskEntity> tempTask2, Map<String, Object> tempMap) {
        StringBuffer stringBuffer = new StringBuffer();
        MpmProjectTaskEntity taskEntity = tempTask2.get(0);
        if (!StringUtil.isEmpty(taskEntity.getNankeepscore())) {
            stringBuffer.append("履约评价得分" + taskEntity.getGuoscore() + "、");
        }
        if (!StringUtil.isEmpty(taskEntity.getNankeeprank())) {
            stringBuffer.append("履约评价排名" + taskEntity.getNankeeprank() + "、");
        }
        if (!StringUtil.isEmpty(taskEntity.getNanmovescore())) {
            stringBuffer.append("运行评价得分" + taskEntity.getNanmovescore() + "、");
        }
        if (!StringUtil.isEmpty(taskEntity.getNanmoverank())) {
            stringBuffer.append("运行评价排名" + taskEntity.getNanmoverank() + "、");
        }
        String nanTask = stringBuffer.toString().substring(0, stringBuffer.length() > 0 ? stringBuffer.length() - 1 : 0);
        tempMap.put("movetask", nanTask);
    }
    /**
     * @description:获取专家拜访反馈
     * @author: houyuhang
     * @date: 2022年12月14日
     * @modify:
     */
    public void queryProjectVisitExpertInfo(List<Map<String, Object>> tempList) {
        String projectcode = tempList.stream().map(item -> "'" + ConvertUtil.convertToString(item.get("projectcode")) + "'").collect(Collectors.joining(","));
        if (StringUtil.isEmpty(projectcode)) {
            return;
        }
        // 获取项目对应的专家拜访反馈信息
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle(" enabled = 1 and projectcode in ({0}) and state = 9 ", projectcode);
        List<MpmProjectVisitExpertEntity> mpmProjectVisitExpertAll = mpmProjectVisitExpertService.queryByWhere(sql.toString());
        Map<String, List<MpmProjectVisitExpertEntity>> expertMap = mpmProjectVisitExpertAll.stream().collect(Collectors.groupingBy(MpmProjectVisitExpertEntity::getProjectcode));
        for (Map<String, Object> tempMap : tempList) {
            if (CollectionUtil.isEmpty(expertMap.get(tempMap.get("projectcode")))) {
                continue;
            }
            int allNum = expertMap.get(tempMap.get("projectcode")).size();
            int actualNum = expertMap.get(tempMap.get("projectcode")).stream().filter(item -> "1".equals(item.getCanreal())).collect(Collectors.toList()).size();
            tempMap.put("visitnum", allNum);
            tempMap.put("enablevisitnum", actualNum);
        }
    }
    /**
     * @description: fds系统获取人员能查看的销售项目数据
     * @author: houyuhang
     * @date: 2022年12月21日
     * @modify:
     */
    public Page listForFMS(Map<String, Object> paraMap, String sql) throws Exception {
        paraMap = BaseUtil.decodeSecureMap(paraMap);
        Query query = new Query(paraMap);
        Map<String, Object> tempMap = new HashMap<>();
        int currentPage = query.getCurrentPage();
        int pageSize = query.getPageSize();
        int begincount = currentPage * pageSize - pageSize;
        tempMap.put("sidx", query.getSidx());
        tempMap.put("sord", query.getSord());
        tempMap.put("pagesize", pageSize);
        tempMap.put("begincount", begincount);
        tempMap.put("whereSql", sql);
        // 查询项目总数
        int totalCount = mpmProjectService.getCount1(tempMap, "queryFdsMapByWhere");
        List<Map<String, Object>> tempList;
        if (query.getPageSize() > 0) {
            tempList = mpmProjectService.queryMapForPage(tempMap, "queryFdsMapForPage");
        } else {
            tempList = mpmProjectService.queryMapByWhere(sql, "queryFdsMapByWhere");
        }
        return new Page(tempList, totalCount, query.getPageSize(), query.getCurrentPage());
    }

    /**
     * @description: 根据用户code获取sessionuserbean信息
     * @author: houyuhang
     * @date: 2023年01月04日
     * @modify:
     */
    public SessionUserBean querySessionUserBean(String usercode) {
        SecurityUserDetails userDetails = (SecurityUserDetails) userDetailsService.loadUserByUsername(usercode);
        SessionUserBean sessionUserBean = new SessionUserBean();
        sessionUserBean.setUser(userDetails.getUser());
        sessionUserBean.setOrg(userDetails.getOrg());
        sessionUserBean.setWorkOrg(userDetails.getWorkOrg());
        sessionUserBean.setDept(userDetails.getDept());
        sessionUserBean.setWorkDept(userDetails.getWorkDept());
        sessionUserBean.setRoleList(userDetails.getRoleList());
        return sessionUserBean;
    }
    /**
     * @description: 导出数据时格式化产品名称字段
     * @author: houyuhang
     * @date: 2023年01月10日
     * @modify:
     */
    public void setProductRangeForExcel(List<Map<String, Object>> tempList) {
        for (Map<String, Object> tempMap : tempList) {
            List<String> tempListStr = (List<String>) tempMap.get("productrangelist");
            if (!CollectionUtil.isEmpty(tempListStr)) {
                tempMap.put("dispproductrange", tempListStr.stream().collect(Collectors.joining("\n")));
            }
        }
    }
    /**
     * @description: 导出数据时获取会议相关信息
     * @author: houyuhang
     * @date: 2023年01月10日
     * @modify:
     */
    public void setMeetingInfoForExcel(List<Map<String, Object>> tempList, String token) {
        List<Map<String, Object>> res = mpmProjectService.queryMeetPerWeek(token, tempList);
        Map<String, List<Map<String, Object>>> resMap = res.stream().collect(Collectors.groupingBy(item -> ConvertUtil.convertToString(item.get("id"))));
        for (Map<String, Object> tempMap : tempList) {
            List<Map<String, Object>> meetList = resMap.get(ConvertUtil.convertToString(tempMap.get("id")));
            if (!CollectionUtil.isEmpty(meetList)) {
                tempMap.put("meetactual", meetList.get(0).get("meetactual"));
                tempMap.put("reviewmeet", meetList.get(0).get("reviewmeet"));
            }
        }
    }
    /**
     * @description: 导出数据时获取标记相关信息
     * @author: houyuhang
     * @date: 2023年01月11日
     * @modify:
     */
    public void setSignInfo(List<Map<String, Object>> tempList) {
        // 获取标记信息
        Map<String, Object> dataMap = new HashMap<>();
        dataMap.put("sourceid", tempList.stream().map(item -> ConvertUtil.convertToString(item.get("id"))).collect(Collectors.joining(",")));
        dataMap.put("sourcetype", "MpmProject");
        dataMap.put("operatetype", "sign");
        dataMap.put("usercode", this.getUserBean().getUser().getCode());
        dataMap.put("sidx", "operatedate");
        dataMap.put("sord", "desc");
        List<Map<String, Object>> signList = baseOperateRecordService.querySignInfo(dataMap);
        Map<String, List<Map<String, Object>>> signMap = signList.stream().collect(Collectors.groupingBy(item -> ConvertUtil.convertToString(item.get("sourceid"))));
        for (Map<String, Object> tempMap : tempList) {
            List<Map<String, Object>> signProjectList = signMap.get(ConvertUtil.convertToString(tempMap.get("id")));
            if (CollectionUtil.isEmpty(signProjectList)) {
                continue;
            }
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append(signProjectList.get(0).get("operateresult"));
            tempMap.put("signinfo", ConvertUtil.convertToString(stringBuilder));
        }
    }
    /**
     * @description:获取产品信息
     * @author: houyuhang
     * @date: 2022年12月14日
     * @modify:
     */
    public void queryProductList(List<Map<String, Object>> tempList) {
        String projectid = tempList.stream().map(item -> "'" + ConvertUtil.convertToString(item.get("id")) + "'").collect(Collectors.joining(","));
        if (StringUtil.isEmpty(projectid)) {
            return;
        }
        // 获取项目对应的内部产品信息
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle(" enabled = 1 and projectid in ({0}) ", projectid);
        List<MpmProjectProductInfoEntity> productEntity = mpmProjectProductInfoService.queryByWhere(sql.toString());
        Map<String, List<MpmProjectProductInfoEntity>> productMap = productEntity.stream().collect(Collectors.groupingBy(item -> ConvertUtil.convertToString(item.getProjectid())));
        for (Map<String, Object> tempMap : tempList) {
            if (CollectionUtil.isEmpty(productMap.get(ConvertUtil.convertToString(tempMap.get("id"))))) {
                continue;
            }
            List<MpmProjectProductInfoEntity> tempProduct = productMap.get(ConvertUtil.convertToString(tempMap.get("id")));
            Map<String, List<MpmProjectProductInfoEntity>> lineMap = tempProduct.stream().collect(Collectors.groupingBy(item -> ConvertUtil.convertToString(item.getProductlinecode())));
            List<String> resList = new ArrayList<>();
            int count = 1;
            // 按照产品线分类显示
            for (Map.Entry<String, List<MpmProjectProductInfoEntity>> entry : lineMap.entrySet()) {
                String productLine = datacodeCache.getValueDynamic("ProductLine", entry.getKey(), "code", "name");
                String cboItemCode = MpmSystemConst.MpmProjectSource.lead.equals(ConvertUtil.convertToString(tempMap.get("projectsource"))) ? "ProductCatalog1" : "ProductCatalog2";
                String product = entry.getValue().stream().map(item -> datacodeCache.getValueDynamic(cboItemCode, item.getProductcode(),"code", "name")).collect(Collectors.joining(";"));
                resList.add(count + "、" + productLine + "：" + product + ";");
                count++;
            }
            tempMap.put("productrangelist", resList);
        }
    }
    /**
     * @description: 获取离职转岗项目变更信息
     * @author: houyuhang
     * @date: 2023年05月05日
     * @modify:
     */
    @Transactional
    public List<Map<String, Object>> queryProjectForChangeManager(String whereSql) {
        List<Map<String, Object>> tempList = mpmProjectService.queryMapByWhere(whereSql, "queryChangeManagerInfo");
        for (Map<String, Object> temp : tempList) {
            this.setDispFields(temp);
        }
        return tempList;
    }
}
